Postgresql: Trying to replace null values using a self join and ending up replacing entire column with first result

user_16627612 0
Viewed 86

Created a self join on matching parcelid's and using the matching parcelid's to populate the missing null values. However, after using the UPDATE function it replaces every row in property_address to the first result.

UPDATE housing
SET property_address = COALESCE(a.property_address, b.property_address)
FROM housing AS a
JOIN housing AS b
    ON a.parcelid = b.parcelid
    AND a.unique_id <> b.unique_id
WHERE a.property_address is null
sql-null sql-update join postgresql
1 Answers

You only need 2 copies of housing so remove the extra join.
Also add a condition in the WHERE clause so that the updated value is not null:

UPDATE housing AS h1
SET property_address = h2.property_address
FROM housing AS h2
WHERE h1.parcelid = h2.parcelid AND h1.unique_id <> h2.unique_id
  AND h1.property_address IS NULL AND h2.property_address IS NOT NULL
user_104988280
Related