update with join statement mysql?

Question

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.

Answer 1

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