UPDATE statement with multiple joins in PostgreSQL
The same as valid UPDATE
statement in Postgres:
UPDATE incode_warrants iwSET warn_docket_no = iv.viol_docket_noFROM incode_warrantvs iwvsJOIN incode_violations iv ON iv.viol_citation_no = iwvs.warnv_citation_no AND iv.viol_viol_no = iwvs.warnv_viol_noWHERE 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:
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:
Your query should look like this:
UPDATE incode_warrantsSET warn_docket_no = incode_violations.viol_docket_noFROM incode_violationsWHERE incode_violations.viol_citation_no = incode_warrants.warnv_citation_noAND 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.
Your update a table, not the join
update incode_warrants ALIASset warn_docket_no = iv.viol_docket_nofrom incode_warrantvs as iw ...