SQL PostgreSQL 中具有多个连接的 UPDATE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32386246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE statement with multiple joins in PostgreSQL
提问by nulltek
I'm trying to update a table called incode_warrantsand set the warn_docket_noto the viol_docket_nofrom the incode_violationstable.
我试图更新一个名为表incode_warrants，并设置warn_docket_no为viol_docket_no从incode_violations表。
I have the following SQL query in Postgres 9.3, but when it fires I get the following error:
我在 Postgres 9.3 中有以下 SQL 查询，但是当它触发时，我收到以下错误：
Error : ERROR: relation "iw" does not exist LINE 1: update iw
Error : ERROR: relation "iw" does not exist LINE 1: update iw
I'm more of an Active Record person so my raw SQL skills are seriously lacking. I was wondering if anyone could help point me in the right direction on how to get this query right.
我更像是一个 Active Record 人，所以我的原始 SQL 技能严重缺乏。我想知道是否有人可以帮助我指出如何正确处理此查询的正确方向。
update iw
set iw.warn_docket_no = iv.viol_docket_no
from incode_warrants as iw
INNER JOIN incode_warrantvs as iwvs
on iw.warn_rid = iwvs.warnv_rid
INNER JOIN incode_violations as iv
ON iv.viol_citation_no = iwvs.warnv_citation_no and iv.viol_viol_no = iwvs.warnv_viol_no
回答by Erwin Brandstetter
The same as valid UPDATEstatement in Postgres:
与UPDATEPostgres 中的有效语句相同：
UPDATE incode_warrants iw
SET warn_docket_no = iv.viol_docket_no
FROM incode_warrantvs iwvs
JOIN incode_violations iv ON iv.viol_citation_no = iwvs.warnv_citation_no
AND iv.viol_viol_no = iwvs.warnv_viol_no
WHERE iw.warn_rid = iwvs.warnv_rid;
You cannot just use a table alias in the FROMclause as target table in the UPDATEclause. The (one!) table to be updated comes right after UPDATE. You can add an alias there if you want. That's the immediate cause of your error message, but there's more.
您不能只在FROM子句中使用表别名作为子句中的目标表UPDATE。要更新的（一个！）表紧跟在UPDATE. 如果需要，您可以在那里添加别名。这是您的错误消息的直接原因，但还有更多。
The column to be updated is always from the one table to be updated and cannot be table-qualified.
要更新的列始终来自要更新的一个表，并且不能是表限定的。
You don't need to repeat the target table in the FROMclause.
您不需要在FROM子句中重复目标表。
回答by Walerian Sobczak
Your query should look like this:
您的查询应如下所示：
UPDATE incode_warrants
SET warn_docket_no = incode_violations.viol_docket_no
FROM incode_violations
WHERE incode_violations.viol_citation_no = incode_warrants.warnv_citation_no
AND incode_violations.viol_viol_no = incode_warrants.warnv_viol_no;
You don't need any other join. With this query you just update a column in one table with values from a column from another table. Of course, it updates only when WHEREcondition is true.
您不需要任何其他加入。使用此查询，您只需使用另一个表中的列中的值更新一个表中的列。当然，它只在WHERE条件为真时更新。
回答by Juan Carlos Oropeza
Your update a table, not the join
您更新表，而不是连接
update incode_warrants ALIAS
set warn_docket_no = iv.viol_docket_no
from incode_warrantvs as iw
...
