UPDATE statement with multiple joins to main table in PostgreSQL

Question

I'm doing conversation from mysql to postgres.

I try to update table EUTMPDFHDT T with join table EUTMPDFH T1 and EUTMPTBLDT T2.

Here is code what I use in mysql.

UPDATE EUTMPDFHDT
SET NWCOLID=T2.NWCOLID
FROM EUTMPDFHDT T
INNER JOIN EUTMPDFH T1 ON T.DFHID = T1.DFHID AND T1.DFHTYP IN ('D','U','S','P','B')
INNER JOIN EUTMPTBLDT T2 ON T.COLID = T2.COLID

In postgres I did try to follow this UPDATE statement with multiple joins in PostgreSQL

But to no avail I cannot solve it because the update statement is not same.

Here what have I done in postgres:

UPDATE EUTMPDFHDT AS T
SET NWCOLID=T2.NWCOLID
FROM 
EUTMPDFH T1
JOIN EUTMPTBLDT T2 ON T.COLID = T2.COLID
WHERE T.DFHID = T1.DFHID AND T1.DFHTYP IN ('D','U','S','P','B');

Here is the error that I hit

ERROR:  invalid reference to FROM-clause entry for table "t"
LINE 5:  JOIN EUTMPTBLDT T2 ON T.COLID = T2.COLID
                               ^
HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
QUERY:  UPDATE EUTMPDFHDT AS T
    SET NWCOLID=T2.NWCOLID
    FROM 
    EUTMPDFH T1
    JOIN EUTMPTBLDT T2 ON T.COLID = T2.COLID
    WHERE T.DFHID = T1.DFHID AND T1.DFHTYP IN ('D','U','S','P','B')

score 8 · Accepted Answer · answered Sep 19 '18 at 06:35

As documented in the manual you should not repeat the target table in the FROM clause of an UPDATE statement.

The FROM clause for an UPDATE unfortunately doesn't follow the exact same rules as the one used in the SELECT clause. It's easier to use the old implicit joins instead of the (usually preferred) explicit JOIN operators.

As far as I can tell this is what you are looking for:

UPDATE eutmpdfhdt as t
  SET nwcolid = t2.nwcolid
FROM eutmpdfh t1, 
     eutmptbldt t2 
WHERE t.dfhid = t1.dfhid 
  AND t.colid = t2.colid  
  AND t1.dfhtyp IN ('D','U','S','P','B')

Weird that only implicit joins can result in bug-free updates. I've never come across this in other SQL languages. — ProgrammingWithRandy, Jan 21 '21 at 21:10

score 0 · Answer 2 · answered Sep 19 '18 at 04:38

You can't include a column from the table you want to update within the ON clause. It must be in the WHERE. If you modify your code like so, I believe it gets you the result you need:

UPDATE
    EUTMPDFHDT AS T
SET
    NWCOLID = T2.NWCOLID
FROM
    EUTMPTBLDT T2
WHERE
    T.COLID = T2.COLID AND
    EXISTS (SELECT 1 FROM EUTMPDFH WHERE DFHID = t.DFHID AND DFHTYP IN ('D','U','S','P','B'));

score 0 · Answer 3 · answered Sep 19 '18 at 06:06

You cannot reference the column of FROm table from ON clause in update query. You can chage the query as follows

UPDATE EUTMPDFHDT AS T
SET NWCOLID=Q.NWCOLID
FROM (SELECT T2.NWCOLID, T.DFHID 
FROM EUTMPDFHDT AS T
JOIN EUTMPDFH T1 ON  T.DFHID = T1.DFHID 
JOIN EUTMPTBLDT T2 ON T.COLID = T2.COLID
WHERE T1.DFHTYP IN ('D','U','S','P','B'))Q
WHERE Q.DFHID=T.DFHID

UPDATE statement with multiple joins to main table in PostgreSQL

3 Answers3