UPDATE statement with multiple joins in PostgreSQL

Question

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

Erwin Brandstetter · Accepted Answer · 2020-06-16T00:43:20.840

87

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:

PostgreSQL: update with left outer self join ignored

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:

How do I (or can I) SELECT DISTINCT on multiple columns?

More in the excellent manual on UPDATE.

edited Jun 16 '20 at 00:43

answered Sep 03 '15 at 22:40

Erwin Brandstetter

605,456
145
1,078
1,228

Excellent answer, thanks for your help and expertise. I'm definitely going to read the manual and improve my technique! – nulltek Sep 03 '15 at 23:20
I did have a question if you don't mind. I'm trying to get this update statement to fire off in a MASSIVE 400 LOC stored proc. I've injected it into the SP and I set a Raise info to note when it fires, but in running the stored proc it never fires. Running the update by itself works fine. Should I create a new question to address this? – nulltek Sep 04 '15 at 18:18
@shakycode: Yes, a new question with all the necessary details is the right move. Comments are not the place. You can always link to this one for context. – Erwin Brandstetter Sep 04 '15 at 18:32
Will do! I need to somehow anonymize the stored proc since it has proprietary schema data in it. I'll be posting soon. Thanks again for your help, Erwin! – nulltek Sep 04 '15 at 18:55

score 7 · Answer 2 · answered Sep 03 '15 at 22:19

7

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.

answered Sep 03 '15 at 22:19

Walerian Sobczak

817
2
10
23

Thanks for the quick answer. When I run it I get this: `Error : ERROR: column incode_warrants.warnv_citation_no does not exist LINE 4: WHERE incode_violations.viol_citation_no = incode_warrants.w...` – nulltek Sep 03 '15 at 22:33
Could you update your question with a schema? I don't knock how your tables look like. Need more explanation. – Walerian Sobczak Sep 03 '15 at 22:41
1

You may have overlooked that `incode_warrants` and `incode_warrantvs` are two different tables - unless that's a typo in the question, of course. – Erwin Brandstetter Sep 03 '15 at 22:47
As Erwin mentioned, this solution is missing `incode_warrantvs` which is why I down voted. – Cale Sweeney Sep 18 '19 at 19:12

score 6 · Answer 3 · edited Jul 23 '20 at 21:22

6

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;

edited Jul 23 '20 at 21:22

Vivek Jain

2,730
6
12
27

answered Jul 23 '20 at 04:23

user2035082

61
1
1

Juan Carlos Oropeza · Answer 4 · 2015-09-04T13:11:41.093

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

edited Sep 04 '15 at 13:11

answered Sep 03 '15 at 22:14

Juan Carlos Oropeza

47,252
12
78
118

1

You cannot table-qualify target columns of an `UPDATE` statement in Postgres. – Erwin Brandstetter Sep 03 '15 at 22:53
@ErwinBrandstetter Thanks for your comment. I fix it. But my answer just address one problem and after seeing your answer realize wasnt a complete answer. – Juan Carlos Oropeza Sep 04 '15 at 13:16

score 1 · Answer 5 · answered Nov 05 '20 at 18:50

That is my code, i generate my join and enclosure in a and after join with my updateable table and in my where i compare my identitys, sorry for my english

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;

UPDATE statement with multiple joins in PostgreSQL

5 Answers5

Linked