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

How to do an update + join in PostgreSQL?

提问by mpen

Basically, I want to do this:

基本上，我想这样做：

update vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id=s.id 
set v.price=s.price_per_vehicle;

I'm pretty sure that would work in MySQL (my background), but it doesn't seem to work in postgres. The error I get is:

我很确定这会在 MySQL 中工作（我的背景），但它似乎在 postgres 中不起作用。我得到的错误是：

ERROR:  syntax error at or near "join"
LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...
                                  ^

Surely there's an easy way to do this, but I can't find the proper syntax. So, how would I write this In PostgreSQL?

当然有一种简单的方法可以做到这一点，但我找不到正确的语法。那么，我将如何在 PostgreSQL 中写这个？

回答by Mark Byers

The UPDATE syntaxis:

该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 ] [, ...] ]

In your case I think you want this:

在你的情况下，我认为你想要这个：

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

回答by Envek

Let me explain a little more by my example.

让我通过我的例子再解释一下。

Task: correct info, where abiturients (students about to leave secondary school) have submitted applications to university earlier, than they got school certificates (yes, they got certificates earlier, than they were issued (by certificate date specified). So, we will increase application submit date to fit certificate issue date.

任务：正确的信息，其中 abiturients（即将离开中学的学生）在他们获得学校证书之前提交了大学申请（是的，他们获得了证书，比他们颁发的证书日期更早（在指定的证书日期之前）。所以，我们将增加申请提交日期以适应证书颁发日期。

Thus. next MySQL-like statement:

因此。下一个类似 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;

Becomes PostgreSQL-like in such a way

以这种方式变得类似 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

As you can see, original subquery JOIN's ONclause have become one of WHEREconditions, which is conjucted by ANDwith others, which have been moved from subquery with no changes. And there is no more need to JOINtable with itself (as it was in subquery).

如您所见，原始 subqueryJOIN的ON子句已成为WHERE条件之一，它AND与其他条件相结合，这些条件已从子查询中移出，没有任何变化。并且不再需要JOIN与自身表（就像在子查询中一样）。

回答by Alvin

The answer of Mark Byers is the optimal in this situation. Though in more complex situations you can take the select query that returns rowids and calculated values and attach it to the update query like this:

在这种情况下，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

This approach lets you develop and test your select query and in two steps convert it to the update query.

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

So in your case the result query will be:

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

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

Note that column aliases are mandatory otherwise PostgreSQL will complain about the ambiguity of the column names.

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

回答by Fast Engy

For those actually wanting to do a JOINyou can also use:

对于那些真正想做的人，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;

You can use the a_alias in the SETsection on the right of the equals sign if needed. The fields on the left of the equals sign don't require a table reference as they are deemed to be from the original "a" table.

SET如果需要，您可以在等号右侧的部分中使用 a_alias 。等号左侧的字段不需要表引用，因为它们被视为来自原始“a”表。

回答by Nate Smith

For those wanting to do a JOIN that updates ONLY the rows your join returns use:

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

This was mentioned above but only through a comment..Since it's critical to getting the correct result posting NEW answer that Works

这是上面提到的，但只是通过评论..因为获得正确的结果发布有效的新答案至关重要

回答by mpen

Here we go:

开始了：

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

Simple as I could make it. Thanks guys!

简单到我可以做到。谢谢你们！

Can also do this:

也可以这样做：

-- Doesn't work apparently
update vehicles_vehicle 
set price=s.price_per_vehicle
from vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id;

But then you've got the vehicle table in there twice, and you're only allowed to alias it once, and you can't use the alias in the "set" portion.

但是，您已经在那里获得了两次车辆表，并且您只能为其设置一次别名，并且您不能在“设置”部分使用别名。

回答by Yusuf

Here's a simple SQL that updates Mid_Name on the Name3 table using the Middle_Name field from Name:

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

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

回答by Alessandro

The link below has a example that resolve and helps understant better how use updateand joinwith postgres.

下面的链接有一个例子，可以解决并帮助更好地理解如何使用update和join使用 postgres。

UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;

See: http://www.postgresqltutorial.com/postgresql-update-join/

请参阅：http: //www.postgresqltutorial.com/postgresql-update-join/

回答by AritraDB

First Table Name: tbl_table1 (tab1). Second Table Name: tbl_table2 (tab2).

第一个表名：tbl_table1 (tab1)。第二个表名：tbl_table2 (tab2)。

Set the tbl_table1's ac_status column to "INACTIVE"

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