SQL 用两张表更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1068447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update with two tables?
提问by Md Nasir Uddin
I am trying to update table A with data from table B. I thought I could do something like:
我正在尝试使用表 B 中的数据更新表 A。我以为我可以执行以下操作：
UPDATE A
SET A.name = B.name
WHERE A.id = B.id
but alas, this does not work.
但唉，这行不通。
Anyone have an idea of how I can do this?
任何人都知道我该如何做到这一点？
回答by John Sansom
Your query does not work because you have no FROMclause that specifies the tables you are aliasing via A/B.
您的查询不起作用，因为您没有指定通过 A/B 别名的表的FROM子句。
Please try using the following:
请尝试使用以下方法：
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A, TableNameB B
WHERE A.ID = B.ID
Personally I prefer to use more explicit join syntax for clarity i.e.
我个人更喜欢使用更明确的连接语法来清晰，即
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A
INNER JOIN TableName B ON
A.ID = B.ID
回答by Nicolas
For Microsoft Access
为了 Microsoft Access
UPDATE TableA A
INNER JOIN TableB B
ON A.ID = B.ID
SET A.Name = B.Name
回答by gus
I was scratching my head, not being able to get John Sansom's Join syntax work, at least in MySQL 5.5.30 InnoDB.
我正在挠头，无法让 John Sansom 的 Join 语法工作，至少在MySQL 5.5.30 InnoDB.
It turns out that this doesn't work.
事实证明这是行不通的。
UPDATE A
SET A.x = 1
FROM A INNER JOIN B
ON A.name = B.name
WHERE A.x <> B.x
But this works:
但这有效：
UPDATE A INNER JOIN B
ON A.name = B.name
SET A.x = 1
WHERE A.x <> B.x
回答by tupan
The answers didn't work for me with postgresql 9.1+
postgresql 9.1+ 的答案对我不起作用
This is what I had to do (you can check more in the manual here)
UPDATE schema.TableA as A
SET "columnA" = "B"."columnB"
FROM schema.TableB as B
WHERE A.id = B.id;
You can omit the schema, if you are using the default schema for both tables.
如果您对两个表都使用默认架构，则可以省略架构。
回答by Awais Afridi
It can be as follows:
它可以如下所示：
UPDATE A
SET A.`id` = (SELECT id from B WHERE A.title = B.title)
