基本上，我想这样做:
update vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id
set v.price=s.price_per_vehicle;
我很确定这在MySQL(我的背景)中可以工作，但在postgres中似乎不起作用。我得到的错误是:
ERROR: syntax error at or near "join"
LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...
^
当然有一个简单的方法来做到这一点，但我找不到合适的语法。那么，我该如何在PostgreSQL中写这个呢?
当前回答
完美的工作! !
带有JOIN的POSTGRE SQL - UPDATE
下面代码-检查列和id的定位如下:
如果你把它放在下面，那么只有它会工作!
---IF you want to update FIRST table
UPDATE table1
SET attribute1 = table2.attribute1
FROM table2
WHERE table2.product_ID = table1.product_ID;
OR
---IF you want to update SECOND table
UPDATE table2
SET attribute1 = table1.attribute1
FROM table1
WHERE table1.product_ID = table2.product_ID;
其他回答
在PostGRE SQL / AWS (SQL工作台)中使用另一个表更新一个表。
在PostGRE SQL中，你需要在UPDATE Query中使用join:
UPDATE TABLEA set COLUMN_FROM_TABLEA = COLUMN_FROM_TABLEB FROM TABLEA,TABLEB WHERE FILTER_FROM_TABLEA = FILTER_FROM_TABLEB;
Example:
Update Employees Set Date_Of_Exit = Exit_Date_Recorded , Exit_Flg = 1 From Employees, Employee_Exit_Clearance Where Emp_ID = Exit_Emp_ID
表A - Employees列表A - Date_Of_Exit,Emp_ID,Exit_Flg表B是- Employee_Exit_Clearance列表B - Exit_Date_Recorded,Exit_Emp_ID
1760行受影响
执行时间:29.18秒
UPDATE语法为:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
在你的情况下，我认为你想要的是:
UPDATE vehicles_vehicle AS v
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id
或者如果你需要连接两个或多个表:
UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
t2.id = t1.t2_id
AND t3.bar = True;
我举个例子再解释一下。
任务:正确的信息，在哪里abiturients(即将离开中学的学生)提交申请大学早于他们获得学校证书(是的，他们获得证书早于他们颁发的证书(指定证书日期)。因此，我们将增加申请提交日期，以适应证书颁发日期。
因此。下一个mysql类语句:
UPDATE applications a
JOIN (
SELECT ap.id, ab.certificate_issued_at
FROM abiturients ab
JOIN applications ap
ON ab.id = ap.abiturient_id
WHERE ap.documents_taken_at::date < ab.certificate_issued_at
) b
ON a.id = b.id
SET a.documents_taken_at = b.certificate_issued_at;
以这样的方式变得像postgresql
UPDATE applications a
SET documents_taken_at = b.certificate_issued_at -- we can reference joined table here
FROM abiturients b -- joined table
WHERE
a.abiturient_id = b.id AND -- JOIN ON clause
a.documents_taken_at::date < b.certificate_issued_at -- Subquery WHERE
可以看到，原来的子查询JOIN的ON子句已经变成了WHERE条件之一，由AND与其他子查询连接，这些子查询已经从子查询中移动，没有任何变化。并且不再需要将表与自身连接(就像在子查询中那样)。
下面是一个简单的SQL，使用Name中的Middle_Name字段更新Name3表中的Mid_Name:
update name3
set mid_name = name.middle_name
from name
where name3.person_id = name.person_id;
对于那些想要做一个JOIN，更新你的连接返回行使用:
UPDATE a
SET price = b_alias.unit_price
FROM a AS a_alias
LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id
WHERE a_alias.unit_name LIKE 'some_value'
AND a.id = a_alias.id
--the below line is critical for updating ONLY joined rows
AND a.pk_id = a_alias.pk_id;
这是上面提到的，但只是通过一个评论..因为它是至关重要的，以获得正确的结果张贴新的答案，工作
