CODEWITHSUNDEEP
mysql
Bart
Bart

Reputation: 4920

update with join statement mysql?

Is this possible in mysql?

update table1 
set column1 = (select column1 from table2  
               inner join table3 
               where table2.column5 = table3.column6);

Here is a a similar question for an Oracle DB.

Upvotes: 7

Views: 10739

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 51000

You can do it. However, in the example you give, there's no JOIN connection between table1 and the source of the values for the update (table2 INNER JOIN table3), so the results will be somewhat unpredictable.

Your query would be something like (I'm not a MySQL expert):

UPDATE table1, table2, table3 SET table1.column1 = table2.column1
    WHERE table2.column5 = table3.column6

but what I think you probably want (I'm just guessing) is something more like:

UPDATE table1, table2, table3 SET table1.column1 = table2.column1
    WHERE table1.somecolumn = table3.somecolumn AND table2.column5 = table3.column6

Upvotes: 13

Related Questions