基本上，我想这样做:
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中写这个呢?
当前回答
在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秒
其他回答
下面是一个简单的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 (postgres)更新选定的列——
UPDATE table1 t1
SET column1 = 'data'
FROM table1
RIGHT JOIN table2
ON table2.id = table1.id
WHERE t1.id IN
(SELECT table2.id FROM table2 WHERE table2.column2 = 12345)
完美的工作! !
带有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;
开始吧:
update vehicles_vehicle v
set price=s.price_per_vehicle
from shipments_shipment s
where v.shipment_id=s.id;
我能做到的最简单。
第一种方式比第二种方式慢。
第一:
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities SET sec_type = 'Unsec'
FROM opportunities AS opp
INNER JOIN accounts AS acc
ON opp.account_id = acc.id
WHERE acc.borrower = true
AND opp.sec_type IS NULL
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;
第二:
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities AS opp
SET sec_type = 'Unsec'
FROM accounts AS acc
WHERE opp.account_id = acc.id
AND opp.sec_type IS NULL
AND acc.borrower = true
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;
