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:
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:
More in the excellent manual on UPDATE
.