Geek Answers Handbook

PostgreSQL UPDATE operation with multiple joins

I am trying to update a table called incode_warrants and set warn_docket_no to 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 I miss the raw SQL skills. I was wondering if anyone could help me in the right direction, how to get this request correctly.

 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
+5
source share
3 answers

Same as the actual 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;

You cannot just use the table alias in the FROM as the target table in the UPDATE clause. The table (one!) To be updated occurs immediately after UPDATE . You can add an alias there if you wish. This is the immediate cause of your error message, but there is more.

The column to be updated is always from the same table, which must be updated, and cannot be qualified according to the table.

You do not need to repeat the target table in the FROM .

All this and more in a great guide.

+11
source

Your table update, not join

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

Your request 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 do not need any other connection. With this query, you simply update a column in one table with the values ​​from a column from another table. Of course, it is updated only when the WHERE true.

+1
source

More articles:


All Articles