MySQL - 基于子查询更新值

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

让我说我有选择，它从table1返回我：

ID  Name
 1  Bob
 2  Alice
 3  Joe

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

然后我希望基于此结果在另一个表中的UPDATE值：

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.

我不知道如何指定WHERE条件。

3 个解决方案

#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

SELECT的结果

#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

请阅读此链接，另一个

#3

Try this

尝试这个

Update table2
Set Name = (Select Name From table1 where table1.ID = table2.ID)
Where table2.ID In (Select ID From table1)