SQL 使用两个表上的连接更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2815953/
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 query with join on two tables
提问by dba_query
I have customerand addresstables.
我有customer和address表。
Query:
询问：
SELECT *
FROM addresses a,
customers b
WHERE a.id = b.id
returns 474 records
返回 474 条记录
For these records, I'd like to add the idof customertable into cidof addresstable.
对于这些记录，我想添加id的customer表成cid的address表。
Example:
If for the first record the id of customer is 9 and idof address is also 9 then i'd like to insert 9 into cid column of address table.
示例：如果对于第一条记录，客户的 id 是 9 id，地址也是 9，那么我想将 9 插入到地址表的 cid 列中。
I tried:
我试过：
UPDATE addresses a,
customers b
SET a.cid = b.id
WHERE a.id = b.id
but this does not seem to work.
但这似乎不起作用。
回答by Michael Buen
this is Postgres UPDATE JOIN format:
这是 Postgres UPDATE JOIN 格式：
UPDATE address
SET cid = customers.id
FROM customers
WHERE customers.id = address.id
Here's the other variations: http://mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html
这是其他变体：http: //mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html
回答by WiredIn
Using table aliases in the join condition:
在连接条件中使用表别名：
update addresses a
set cid = b.id
from customers b
where a.id = b.id
回答by Thomas
Officially, the SQL languages does not support a JOIN or FROM clause in an UPDATE statement unless it is in a subquery. Thus, the Hoyle ANSI approach would be something like
正式地，SQL 语言不支持 UPDATE 语句中的 JOIN 或 FROM 子句，除非它在子查询中。因此，Hoyle ANSI 方法类似于
Update addresses
Set cid = (
Select c.id
From customers As c
where c.id = a.id
)
Where Exists (
Select 1
From customers As C1
Where C1.id = addresses.id
)
However many DBMSs such Postgres support the use of a FROM clause in an UPDATE statement. In many cases, you are required to include the updating table and alias it in the FROM clause however I'm not sure about Postgres:
然而，许多 DBMS（例如 Postgres）支持在 UPDATE 语句中使用 FROM 子句。在许多情况下，您需要在 FROM 子句中包含更新表和别名，但是我不确定 Postgres：
Update addresses
Set cid = c.id
From addresses As a
Join customers As c
On c.id = a.id
回答by monn
update addresses set cid=id where id in (select id from customers)
回答by VITHAL PATIL
Try this one
试试这个
UPDATE employee
set EMPLOYEE.MAIDEN_NAME =
(SELECT ADD1
FROM EMPS
WHERE EMP_CODE=EMPLOYEE.EMP_CODE);
WHERE EMPLOYEE.EMP_CODE >='00'
AND EMPLOYEE.EMP_CODE <='ZZ';
