CODEWITHSUNDEEP
postgresqlsql-updateself-join
Jade Young
Jade Young

Reputation: 123

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

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 ^

Upvotes: 1

Views: 630

Answers (1)

wildplasser
wildplasser

Reputation: 44250

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
  ;

Upvotes: 2

Related Questions