UPDATE statement with multiple joins in PostgreSQL

问题:

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

回答1:

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.

回答2:

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 WHERE condition is true.

回答3:

Your update a table, not the join

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