mysql update from 子查询,MySQL-根据子查询更新值

let's say I have select, which return me from table1:

ID Name

1 Bob

2 Alice

3 Joe

Then I want UPDATE values in another table based on this result:

UPDATE table2 SET Name = table1.Name WHERE ID = table1.ID

As I understood, I can only do internal select in one place, like:

UPDATE table2 SET Name = (select Name from table1) WHERE ...

And I don't know how to specify WHERE-condition.

解决方案

all you should do is just join the tables like this.

UPDATE table2 t2

JOIN table1 t1 ON t1.id = t2.id

SET t2.name = t1.name;

if you are set on doing it with a select you could do it like this.

UPDATE table2 t2,

( SELECT Name, id

FROM table1

) t1

SET t2.name = t1.name

WHERE t1.id = t2.id