I have customer
and address
tables.
我有客户和地址表。
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 id
of customer
table into cid
of address
table.
对于这些记录,我想将customer表的id添加到地址表的cid中。
Example: If for the first record the id of customer is 9 and id
of address is also 9 then i'd like to insert 9 into cid column of address table.
示例:如果对于第一条记录,customer的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.
但这似乎不起作用。
5 个解决方案
#1
63
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
#2
4
Using table aliases in the join condition:
在连接条件中使用表别名:
update addresses a
set cid = b.id
from customers b
where a.id = b.id
#3
3
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
#4
0
update addresses set cid=id where id in (select id from customers)
#5
-3
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';
#1
63
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
#2
4
Using table aliases in the join condition:
在连接条件中使用表别名:
update addresses a
set cid = b.id
from customers b
where a.id = b.id
#3
3
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
#4
0
update addresses set cid=id where id in (select id from customers)
#5
-3
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';