Question 218462 in PostgreSQL Question posted in
The official documentation can be found here.

Using COALESCE in postgresql with UPDATE

I have a table of real estate data with lots of NULLS in address column. I want to replace these nulls based on the unique ID. (I’m following Alex the Analyst tutorial). However when I use this in postgresql, I’m getting an error:

ERROR: relation "a" does not exist

UPDATE a SET
propertyaddress = COALESCE(a.propertyaddress, b.propertyaddress)
FROM nashville_housing_data a
JOIN nashville_housing_data b
  ON a.parcelid = b.parcelid
AND a.uniqueid <> b.uniqueid
WHERE propertyaddress is null

Now, when I replaced a with the table name, it updated all address rows to one specific address.

Tags:

2

Answers


  1. 0

    You can’t UPDATE aliases; you must use the table name.

    Also, COALESCE is useless here because the condition WHERE propertyaddress is null guarantees the first term is always null, so just assign b.propertyaddress.

    Try this:

    UPDATE nashville_housing_data SET
propertyaddress = b.propertyaddress
FROM nashville_housing_data
JOIN nashville_housing_data b
  ON nashville_housing_data.parcelid = b.parcelid
AND nashville_housing_data.uniqueid <> b.uniqueid
WHERE nashville_housing_data.propertyaddress is null

    If there can be more than one other rows with a matching parcelid, break the tie with MAX():

    ...
propertyaddress = MAX(b.propertyaddress)
...
    Login or Signup to reply.
  2. 0

    The Issue here is with the table alias ‘a’. You cannot directly reference the updated table using an alias in the UPDATE statement. Instead, it would help if you directly referenced the table name. Try using the query with the alias ‘a’.

    UPDATE nashville_housing_data
SET propertyaddress = COALESCE(propertyaddress, b.propertyaddress)
FROM nashville_housing_data b
WHERE nashville_housing_data.propertyaddress IS NULL
  AND nashville_housing_data.parcelid = b.parcelid
  AND nashville_housing_data.uniqueid <> b.uniqueid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search