在PostgreSQL中使用多个连接的UPDATE语句

Question

我正试图更新名为incode_warrants的表格，并将warn_docket_no设置为来自incode_violations表格中的viol_docket_no。

我在Postgres 9.3中有以下SQL查询语句，但当它执行时，我会得到以下错误：

Error : ERROR:  relation "iw" does not exist
LINE 1: update iw

我更偏向于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

- nulltek

Answer 1

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

在UPDATE子句中，不能只使用表别名作为目标表。被更新的（唯一）表紧随UPDATE关键字之后（如果我们忽略中间可能有的ONLY关键字）。如果需要，您可以在此处添加别名。这是导致错误消息的直接原因，但还有更多细节需要注意。

要更新的列始终来自于被更新的那个表，并且不能加上表限定符。

您不需要在FROM子句中重复目标表 - 除非像这种特殊情况：

• PostgreSQL: update with left outer self join ignored

这个可选部分可以通过抑制没有改变任何内容的更新来避免无意义的成本:

AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no

请参见：

• 如何在多列上选择 DISTINCT？

有关 UPDATE 的更多详细信息，请查看优秀手册。

- Erwin Brandstetter

---

非常好的答案，感谢您的帮助和专业技能。我一定会阅读手册并改进我的技术！ - nulltek

我有一个问题，如果您不介意的话。我正在尝试让这个更新语句在一个巨大的400行存储过程中触发。我已经将其注入到存储过程中，并设置了一个Raise info来记录它何时被触发，但运行存储过程时它从未被触发。单独运行更新语句是正常的。我应该创建一个新的问题来解决这个吗？ - nulltek

@shakycode：是的，提出一个包含所有必要细节的新问题是正确的做法。评论不是合适的地方。您可以随时链接到此处以获取上下文。 - Erwin Brandstetter

好的！我需要以某种方式对存储过程进行匿名化处理，因为其中包含了专有的模式数据。我很快就会发布。再次感谢您的帮助，Erwin！ - nulltek

Answer 2

您的查询应该长这样：

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;

您不需要其他的连接操作。使用此查询，您只需从另一个表的一列中获取值，然后更新一个表中的一列。当然，它仅在WHERE条件为真时进行更新。

- Walerian Sobczak

---

感谢您的快速回复。当我运行它时，出现了这个错误：错误: 列 incode_warrants.warnv_citation_no 不存在 第4行: WHERE incode_violations.viol_citation_no = incode_warrants.w... - nulltek

你能否更新一下你的问题并提供一个模式图？我不知道你的表长什么样子，需要更多的解释。 - Walerian Sobczak

您可能忽略了incode_warrants和incode_warrantvs是两个不同的表格，除非这在问题中是一个笔误。 - Erwin Brandstetter

正如Erwin所提到的，这个解决方案缺少incode_warrantvs，这就是我投反对票的原因。 - Cale Sweeney

Answer 3

UPDATE incode_warrants iw
     SET warn_docket_no = iv.viol_docket_no
FROM incode_warrantvs AS iwvs, incode_violations AS iv 
WHERE iv.viol_citation_no = iwvs.warnv_citation_no AND
      iv.viol_viol_no = iwvs.warnv_viol_no AND
      iw.warn_rid = iwvs.warnv_rid;

- user2035082

Answer 4

您需要更新表，而不是连接

update incode_warrants ALIAS
set warn_docket_no = iv.viol_docket_no
from incode_warrantvs as iw 
...

- Juan Carlos Oropeza

---

在Postgres中，您不能在“UPDATE”语句中使用表限定符来指定目标列。 - Erwin Brandstetter

@ErwinBrandstetter 感谢您的评论。我已经修复了它。但是我的答案只解决了一个问题，看到您的答案后意识到这不是一个完整的答案。 - Juan Carlos Oropeza

Answer 5

这是我的代码，我在a中生成我的连接和封闭，并在更新表格后与之连接，在我的where子句中比较我的标识符，抱歉我的英语不好。

update cs_ticket ct
set site_name = a.name
from (
    select ct.id, cs."name" 
    from cs_ticket ct 
    inner join cs_net_segment cns 
    on ct.affected_ip like cns.ip || '.%'
    and end_date is null
    inner join cs_site cs 
    on cs.id = cns.site_id)a
where ct.id = a.id;

- Dave Rincon