MySQL - Update values based on subquery

Question

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.

Answer 1

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; 

RESULTS WITH JOIN

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 

RESULTS FROM SELECT

Answer 2

 UPDATE table2  SET name = (SELECT table1.Name FROM table1 WHERE table1.id = table2.id)  WHERE apply_condition 

EDIT:#1

   UPDATE table2 t2, (SELECT id, name FROM table1) t1 SET t2.name = t1.name WHERE t1.id = t2.id 

please read this link,another