blocks|key|749688|text|让我用我的例子进一步解释一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|749689|任务:更正信息，其中申请人(即将离开中学的学生)提交大学申请的时间早于获得学校证书的时间(是的，他们获得证书的时间早于颁发证书的时间(在指定的证书日期之前)。因此，我们将增加申请提交日期以适应证书颁发日期。|749690|因此。下一条类似MySQL的语句：|749691|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;|code-block|syntax|javascript|749692|以这样的方式变得像PostgreSQL|749693|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|749694|正如你所看到的，原始子查询JOIN的ON子句已经变成了WHERE条件之一，它是由AND和其他条件连接在一起的，这些条件从子查询中移出而没有任何变化，并且不再需要像在子查询中那样对表本身进行JOIN。|offset|length|style|CODE|749695|entityMap^0|0|0|0|0|0|0|D|4|I|2|R|5|14|3|2M|4|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|12|8|@$Q|13|R|14|S|T]|$Q|15|R|16|S|T]|$Q|17|R|18|S|T]|$Q|19|R|1A|S|T]|$Q|1B|R|1C|S|T]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$]]

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:


<pre class="lang-sql prettyprint-override"><code>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 &lt; ab.certificate_issued_at
) b
ON a.id = b.id
SET a.documents_taken_at = b.certificate_issued_at;
</code></pre>

Becomes PostgreSQL-like in such a way

<pre class="lang-sql prettyprint-override"><code>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 &lt; b.certificate_issued_at -- Subquery WHERE
</code></pre>

As you can see, original subquery <code>JOIN</code>'s <code>ON</code> clause have become one of <code>WHERE</code> conditions, which is conjucted by <code>AND</code> with others, which have been moved from subquery with no changes. And there is no more need to <code>JOIN</code> table with itself (as it was in subquery).

blocks|key|749796|text|对于那些真正想要做JOIN的人，你也可以使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|749797|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;|code-block|syntax|javascript|749798|如果需要，您可以在等号右侧的SET部分中使用a_alias。等号左边的字段不需要表引用，因为它们被认为来自原始的"a“表。|749799|entityMap^0|9|4|0|0|E|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

For those actually wanting to do a <code>JOIN</code> you can also use:

<pre><code>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;
</code></pre>

You can use the a_alias in the <code>SET</code> section 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.

blocks|key|749859|text|下面是一个简单的SQL，它使用名称中的Middle_Name字段更新Name3表上的Mid_Name：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|749860|update+name3
set+mid_name+=+name.middle_name
from+name
where+name3.person_id+=+name.person_id;|code-block|syntax|javascript|749861|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>update name3
set mid_name = name.middle_name
from name
where name3.person_id = name.person_id;
</code></pre>

blocks|key|749925|text|在这种情况下，Mark+Byer的答案是最优的。不过，在更复杂的情况下，您可以使用返回rowids和计算值的select查询，并将其附加到更新查询，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|749926|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|code-block|syntax|javascript|749927|这种方法允许您开发和测试select查询，并通过两个步骤将其转换为update查询。|749928|因此，在您的示例中，结果查询将为：|749929|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|749930|请注意，列别名是强制的，否则PostgreSQL将报告列名的多义性。|749931|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>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
</code></pre>

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:

<pre><code>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
</code></pre>

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

blocks|key|749981|text|对于那些想要执行只更新连接返回的行的连接的人，可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|749982|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;|code-block|syntax|javascript|749983|上面提到了这一点，但只有通过comment..Since才能获得正确的结果，并发布有效的新答案|749984|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>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;
</code></pre>

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

blocks|key|750010|text|下面的链接有一个例子，可以解决和帮助理解如何在postgres中更好地使用update和join。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|750011|UPDATE+product
SET+net_price+=+price+-+price+*+discount
FROM
product_segment
WHERE
product.segment_id+=+product_segment.id;|code-block|syntax|javascript|750012|请参阅：http://www.postgresqltutorial.com/postgresql-update-join/|750013|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresqltutorial.com/postgresql-update-join/^0|11|6|18|4|0|0|4|1L|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@$9|11|A|12|1|13]]|E|$]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

The link below has a example that resolve and helps understant better how use <code>update</code> and <code>join</code> with postgres.

<pre><code>UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;
</code></pre>

See: <a href="http://www.postgresqltutorial.com/postgresql-update-join/" rel="nofollow noreferrer">http://www.postgresqltutorial.com/postgresql-update-join/</a>

blocks|key|533533|text|第一个表名:+tbl_table1+(tab1)。第二个表名:+tbl_table2+(tab2)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|533534|将tbl_table1的ac_status列设置为"INACTIVE“|533535|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';|code-block|syntax|javascript|533536|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

Set the tbl_table1's ac_status column to "INACTIVE"

<pre><code>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';
</code></pre>

Basically, I want to do this:

<pre><code>update vehicles_vehicle v 
 join shipments_shipment s on v.shipment_id=s.id 
set v.price=s.price_per_vehicle;
</code></pre>

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:

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

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?

How to do an update + join in PostgreSQL?

PostgreSQL

MySQL

基本上，我想这样做：update vehicles_vehicle v     join shipments_shipment s on v.shipment_id=s.id set v.price=s.price_per_vehicle;我非常确定这在MySQL (我的背景)下是可行的，但在postgres中似乎行不...

问如何在PostgreSQL中执行更新+ join？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中执行更新+ join？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中执行更新+ join？
EN