Compare And Update

Discussion in 'Databases' started by webboy, Oct 21, 2010.

0
  1. #1
    Hi all just a questions

    I wish to compare two tables against each other and then update a value in table 1 if there is match from table 2. but I get the error the column does not exist for table 2 what have i done wrong ?

    UPDATE table1
    SET table1.row = 'y'
    WHERE table1.row = table2.row
     
    webboy, Oct 21, 2010 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    You need to join the tables, otherwise the database has no idea what table2 is. Make sure you backup the tables before performing this. Without seeing the structure, I can't give a 100% accurate query.

    UPDATE
    table1
    INNER JOIN table2 ON table1.some_column = table2.some_column
    SET table1.row = 'y'
     
    jestep, Oct 21, 2010 IP
  3. webboy

    webboy Peon

    Messages:
    109
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thank Boss Well Done!!!.. Long sleepless nights got the best of me on not thinking about inner join
     
    webboy, Oct 21, 2010 IP