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')

Answer 1

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')