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

本文翻译自：How to do an update + join in PostgreSQL?

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.

我很确定这可以在MySQL（我的背景）中使用，但似乎在postgres中不起作用。

The error I get is:

我得到的错误是：

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中编写此代码？

#1楼

参考：https://stackoom.com/question/X1Eu/如何在PostgreSQL中进行更新-加入

#2楼

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.

任务：正确的信息，即那些有进取心的人（即将离开中学的学生）比他们获得学校证书的时间更早提交申请（是的，他们比得到证书的时间更早（按指定的证书日期）获得证书。所以，我们将增加申请提交日期以适合证书签发日期。

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

ON

clause have become one of

WHERE

conditions, which is conjucted by

AND

with others, which have been moved from subquery with no changes.

如您所见，原始子查询

JOIN

的

ON

子句已成为

WHERE

条件之一，由

AND

与其他条件结合在一起，这些条件已从子查询中移出，没有任何更改。

And there is no more need to

JOIN

table with itself (as it was in subquery).

并且不再需要将表本身与

JOIN

（就像在子查询中一样）。

#3楼

For those actually wanting to do a

JOIN

you 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

SET

section on the right of the equals sign if needed.

如果需要，可以在等号右侧的

SET

部分中使用a_alias。

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.

等号左侧的字段不需要引用表，因为它们被视为来自原始的“ a”表。

#4楼

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.

但是随后您在那儿有两次车辆表，并且只允许给它别名一次，并且不能在“设置”部分中使用别名。

#5楼

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;
           

#6楼

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:

尽管在更复杂的情况下，您可以采用选择查询来返回行标识和计算值，并将其附加到更新查询中，如下所示：

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 t1
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将抱怨列名的歧义。