PostgreSQL 中具有多个连接的 UPDATE 语句 [英] UPDATE statement with multiple joins in PostgreSQL

问题描述

我正在尝试更新名为 incode_warrants 的表，并将 warn_docket_no 设置为 incode_violations 中的 viol_docket_no> 表.

我在 Postgres 9.3 中有以下 SQL 查询，但是当它触发时出现以下错误:

<块引用>

错误:错误:关系iw"不存在第 1 行:更新 iw

我更像是一个 Active Record 人，所以我的原始 SQL 技能严重缺乏.我想知道是否有人可以帮助我指出正确的方向，了解如何正确进行此查询.

更新 iw设置 iw.warn_docket_no = iv.viol_docket_no从 incode_warrants 作为 iwINNER JOIN incode_warrantvs 作为 iwvs在 iw.warn_rid = iwvs.warnv_ridINNER JOIN incode_violations as ivON iv.viol_citation_no = iwvs.warnv_citation_no 和 iv.viol_viol_no = iwvs.warnv_viol_no

解决方案

与Postgres中有效的UPDATE语句相同:

更新 incode_warrants iwSET warn_docket_no = iv.viol_docket_noFROM incode_warrantvs iwvs加入 incode_violations iv ON iv.viol_citation_no = iwvs.warnv_citation_noAND iv.viol_viol_no = iwvs.warnv_viol_no在哪里 iw.warn_rid = iwvs.warnv_rid;-- 并且 iw.warn_docket_no 与 iv.viol_docket_no 不同 -- 见下文

您不能只使用 FROM 子句中的表别名作为 UPDATE 子句中的目标表.要更新的(一个！)表紧跟在 UPDATE 关键字之后(如果我们忽略中间可能的 ONLY 关键字).如果需要，您可以在此处添加别名.这是您的错误消息的直接原因，但还有更多.

要更新的列总是来自一个要更新的表，不能是表限定的.

您不需要在 FROM 子句中重复目标表 - 像这样的特殊情况除外:

• PostgreSQL:忽略左外自连接更新

此可选添加可以通过抑制不改变任何内容的更新来避免无意义的成本:

AND iw.warn_docket_no 与 iv.viol_docket_no 不同

见:

• 我该怎么做(或者我可以)在多列上选择 DISTINCT 吗?

UPDATE 优秀手册中的更多内容.

I'm trying to update a table called incode_warrants and set the warn_docket_no to the viol_docket_no from the incode_violations table.

I have the following SQL query in Postgres 9.3, but when it fires I get the following error:

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.

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

解决方案

The same as valid UPDATE statement in Postgres:

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;
-- AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no -- see below

You cannot just use a table alias in the FROM clause as target table in the UPDATE clause. The (one!) table to be updated comes right after UPDATE keyword (if we ignore a possible ONLY keyword in between). You can add an alias there if you want. That's the immediate cause of your error message, but there's more.

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 FROM clause - except for special cases like this:

• PostgreSQL: update with left outer self join ignored

This optional addition can avoid pointless cost by suppressing updates that do not change anything:

AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no

See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

More in the excellent manual on UPDATE.

这篇关于PostgreSQL 中具有多个连接的 UPDATE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！