#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    2
    Rep Power
    0

    SQL update one table from another


    Hi people
    This is probably really dumb but it has me stumpted

    I want to update column from another tables column

    eg

    table1 table2

    name link name link

    square 1 circle x
    circle 2 hexagon y
    hexagon 3 square z

    In this case i want to replace all the info from table1.link to table2.link where the name is the same.

    So table2.link would end up as '2' ,hexagon.link = '3' etc

    I keep getting stuck as update dosnt seem to let me refer to two tables.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    2
    Rep Power
    0
    Sorry guys I finally worked it out

    if anyone cares how

    UPDATE Table2
    SET link =
    (SELECT link
    FROM table1
    WHERE table1.name = table2.name)

    and i found what i wanted here

    http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst02.htm

    which has some good examples if your new, tho its DB2
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    or this --
    Code:
    update table2
       set link= t1.link
      from table2 t2
    inner
      join table1 t1
        on t2.name = t1.name
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo