Professional Web Applications Themes

update from different tables - MySQL

I need help for update a table in this db: tab1: id F1 1 45 2 52 3 10 4 25 .... tab2: id RIFTAB1 F1_2 1 3 45 2 4 12 3 4 10 4 1 40 5 2 80 .... I want to update every F1 in tab1 with min value of F1_2 where tab2.RIFTAB1=tab1.id : tab1: id F1 1 40 2 80 3 45 4 10 I tried this: UPDATE TAB1 SET F1= (SELECT MIN(F1_2) FROM TAB2 WHERE ???? Thank you in advance. bye, max...

  1. #1

    Default update from different tables

    I need help for update a table in this db:

    tab1:
    id F1
    1 45
    2 52
    3 10
    4 25
    ....
    tab2:
    id RIFTAB1 F1_2
    1 3 45
    2 4 12
    3 4 10
    4 1 40
    5 2 80
    ....

    I want to update every F1 in tab1 with min value of F1_2 where
    tab2.RIFTAB1=tab1.id :
    tab1:
    id F1
    1 40
    2 80
    3 45
    4 10

    I tried this:
    UPDATE TAB1 SET F1= (SELECT MIN(F1_2) FROM TAB2 WHERE ????

    Thank you in advance.

    bye,
    max


    Massimo Guest

  2. #2

    Default Re: update from different tables

    == Quote from Massimo (it)'s article 

    what version of mysql are you running? if you are on 5.x you can write a stored
    procedure to do this very easily!
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: update from different tables


    "lark" <net> ha scritto nel messaggio
    news:8szci.63$news.prodigy.net... 
    stored 

    I have v 5. I already think that it need a stored procedure, I wished to
    know if it was possible to do it without SP. So, it seems it's not possible.

    thanx a lot

    by,
    max


    Massimo Guest

  4. #4

    Default Re: update from different tables

    On Fri, 15 Jun 2007 11:56:12 +0200, "Massimo" wrote:
     

    update tab1 set tab1.F1 = (
    select min(tab2.F1_2) from tab2 where tab2.RIFTAB1 = tab1.id
    )
    --
    Ross McKay, Toronto, NSW Australia
    "Let the laddie play wi the knife - he'll learn"
    - The Wee Book of Calvin
    Ross Guest

  5. #5

    Default Re: update from different tables


    "Ross McKay" <NOT.THIS.BIT.org.au> ha scritto nel messaggio
    news:com... 

    It works!

    thank you

    bye,
    max


    Massimo Guest

Similar Threads

  1. Update multiple tables
    By Bryano1621 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: July 8th, 07:10 AM
  2. Replies: 4
    Last Post: April 26th, 07:38 PM
  3. how to update joined tables?
    By mike2004 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 7th, 08:17 PM
  4. Update Stats not updating for all the Tables
    By KalpanaPai in forum Informix
    Replies: 1
    Last Post: September 8th, 02:42 AM
  5. UPDATE from Two Tables Into One Table
    By Jon in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 23rd, 06:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139