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

问题描述

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

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

  Error：错误：关系 iw 不存在
第1行：更新iw 
  

我更喜欢Active Record，所以我的SQL原始技能严重缺乏。我想知道是否有人可以帮助我指出正确的查询方向。

  update iw 
将来自incode_warrants的iw.warn_docket_no = iv.viol_docket_no = iv设为iw 
 INNER JOIN将incode_warrantvs设为iwvs 
于iw.warn_rid = iwvs.warnv_rid 
 INNER_ININ in_code_violations as b ON iv.viol_citation_no = iwvs.warnv_citation_no和iv.viol_viol_no = iwvs.warnv_viol_no 
  

解决方案

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

  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.warnvv_vol_vol_vol_noviol_noviol_no viol_nool = no wol_nool = no wol_col_vol_no_ol = no 
 and iv。 b $ b在哪里iw.warn_rid = iwvs.warnv_rid; 
  

您不能只在 FROM 子句作为 UPDATE 子句中的目标表。 UPDATE 之后立即更新要更新的（一个！）表。您可以根据需要在此处添加别名。这是导致您出现错误消息的直接原因，但是还有更多的原因。

要更新的列始终来自要更新的一个表，并且不能通过表限定。 / p>

您无需在 FROM 子句中重复目标表。

所有这些内容以及精美手册中的更多内容。

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;

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. 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.

All of that and more in the excellent manual.

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