基本上，我想这样做:
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中写这个呢?
当前回答
第一个表名:tbl_table1 (tab1)。 第二表名:tbl_table2 (tab2)。
将tbl_table1的ac_status列设置为“INACTIVE”
update common.tbl_table1 as tab1
set ac_status= 'INACTIVE' --tbl_table1's "ac_status"
from common.tbl_table2 as tab2
where tab1.ref_id= '1111111'
and tab2.rel_type= 'CUSTOMER';
其他回答
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;
下面的链接提供了一个示例，可以帮助您更好地理解如何使用update和join postgres。
UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;
参见:http://www.postgresqltutorial.com/postgresql-update-join/
对于那些真正想要做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;
如果需要，可以在等号右侧的SET部分中使用a_alias。 等号左边的字段不需要表引用，因为它们被认为来自原始的“a”表。
我举个例子再解释一下。
任务:正确的信息，在哪里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与其他子查询连接，这些子查询已经从子查询中移动，没有任何变化。并且不再需要将表与自身连接(就像在子查询中那样)。
第一种方式比第二种方式慢。
第一:
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; $$;
