[FIXED] How to UPDATE a column from a self join table in PostgreSQL?

Issue

I need to replace a column by a column from a self join table but I got an error. What should I do? Thanks!

UPDATE a
SET propertyaddress=COALESCE(a.propertyaddress,b.propertyaddress)
FROM Housing a
JOIN Housing b
  ON a.ParcelID=b.ParcelID
  AND a.uniqueid!=b.uniqueid
WHERE a.propertyaddress IS NULL); 

ERROR: relation "a" does not exist LINE 32: UPDATE a ^

Solution

You were using the Microsoft-Syntax for UPDATE. (and you don't need the COALESCE(), since a.propertyaddress is always NULL)

Postgres documentation for UPDATE

---

UPDATE Housing a
SET propertyaddress = b.propertyaddress
FROM Housing b
WHERE a.ParcelID = b.ParcelID
  AND a.uniqueid <> b.uniqueid
  AND a.propertyaddress IS NULL
  ; 

Answered By - wildplasser

Answer Checked By - Mildred Charles (PHPFixing Admin)