如何在PostgreSQL中进行更新+加入？

小开

最佳答案

[ 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

小开

让我用我的例子再解释一下。

任务：正确的信息，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与其他条件组合而成，这些条件已从子查询中移动，没有任何更改。并且不再需要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”表。

小开

我们开始吧：

update vehicles_vehicle v
set price=s.price_per_vehicle
from shipments_shipment s
where v.shipment_id=s.id;

就像我能做到的那样简单。

小开

下面是一个简单的SQL，使用Name中的Middle_Name字段更新Name3表上的Mid_Name：

update name3
set mid_name = name.middle_name
from name
where name3.person_id = name.person_id;

小开

在这种情况下，Mark Byers的答案是最佳的。虽然在更复杂的情况下，您可以使用返回rowids和计算值的选择查询并将其附加到更新查询中，如下所示：

with t as (
-- Any generic query which returns rowid and corresponding calculated values
select t1.id as rowid, f(t2, t2) as calculatedvalue
from table1 as t1
join table2 as t2 on t2.referenceid = t1.id
)
update table1
set value = t.calculatedvalue
from t
where id = t.rowid

此方法允许您开发和测试选择查询，并通过两个步骤将其转换为更新查询。

因此，在您的情况下，结果查询将是：

with t as (
select v.id as rowid, s.price_per_vehicle as calculatedvalue
from vehicles_vehicle v
join shipments_shipment s on v.shipment_id = s.id
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

请注意，列别名是强制性的，否则PostgreSQL会抱怨列名的歧义。

小开

对于那些想要执行只更新您的连接返回的行的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;

这是上面提到的，但只有通过评论…因为它的关键是得到正确的结果发布新的答案，工程

小开

下面的链接有一个示例，可以解决并帮助更好地理解如何将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/

小开

第一个表名称：tbl_table1（tab1）。第二个表名称：tbl_table2（tab2）。

将tbl_table1ac_status列设置为"非活动"

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';

小开

为了给上面所有的答案添加一些非常重要的东西，当你想更新join-table时，你可能会遇到两个问题：

不能使用要更新到JOIN另一个表
Postgres希望在JOIN之后有一个ON子句，所以你不能只使用where子句。

这意味着基本上，以下查询无效：

UPDATE join_a_b
SET count = 10
FROM a
JOIN b on b.id = join_a_b.b_id -- Not valid since join_a_b is used here
WHERE a.id = join_a_b.a_id
AND a.name = 'A'
AND b.name = 'B'

UPDATE join_a_b
SET count = 10
FROM a
JOIN b -- Not valid since there is no ON clause
WHERE a.id = join_a_b.a_id
AND b.id = join_a_b.b_id
a.name = 'A'
AND b.name = 'B'

相反，您必须像这样使用FROM子句中的所有表：

UPDATE join_a_b
SET count = 10
FROM a, b
WHERE a.id = join_a_b.a_id
AND b.id = join_a_b.b_id
AND a.name = 'A'
AND b.name = 'B'

这对一些人来说可能很简单，但我被困在这个问题上，想知道发生了什么，所以希望它能帮助其他人。

小开

--目标：使用连接（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)

小开

要使用另一个表更新一个表，请在PostGRESQL/AWS（SQL工作台）中。

在PostGRESQL中，这是在UPDATE Query中使用连接的方式：

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-表A中的员工列-Date_Of_Exit、Emp_ID、Exit_Flg表B中的Employee_Exit_Clearance列-Exit_Date_Recorded、Exit_Emp_ID

1760行受到影响

执行时间：29.18s

小开

第一种方式比第二种方式慢。

第一：

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; $$;

小开

工作完美！！！

POSTGRESQL-通过加入更新

下面的代码-检查列和ID的位置如下：

如果你把它完全如下，那么只有它会工作！

---IF you want to update FIRST table
UPDATE table1
SET attribute1 = table2.attribute1
FROM table2
WHERE table2.product_ID = table1.product_ID;

或

---IF you want to update SECOND table
UPDATE table2
SET attribute1 = table1.attribute1
FROM table1
WHERE table1.product_ID = table2.product_ID;