0

SQL query (PostgreSQL) looks like that:

UPDATE a
SET "PropertyAddress" = COALESCE(a."PropertyAddress", b."PropertyAddress")
FROM "NashvilleHousingData" a
INNER JOIN "NashvilleHousingData" b
    ON a."ParcelID" = b."ParcelID"
    AND a."UniqueID" <> b."UniqueID"
WHERE a."PropertyAddress" IS NULL;

And the error is relation "a" does not exist

I tried other advices about the notion in the code public or scheme, but it still doesn't work. Please, help

eliz
  • 1
  • 1
  • Does this answer your question? [updating table rows in postgres using subquery](https://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery) – astentx Jul 11 '21 at 22:49

1 Answers1

2

This is not how Postgres handles updates with more than one table. You don't repeat the table in the from clause:

UPDATE "NashvilleHousingData" nhd
    SET "PropertyAddress" = COALESCE(nhd."PropertyAddress", nhd2."PropertyAddress")
FROM "NashvilleHousingData" nhd2
WHERE nhd2."ParcelID" = nhd."ParcelID" AND
      nhd2."UniqueID" <> nhd."UniqueID"
WHERE nhd."PropertyAddress" IS NULL;

Also, the COALESCE() is superfluous because the value is known to be NULL:

    SET "PropertyAddress" = nhd2."PropertyAddress"
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your answer! But I need to use JOIN in order to unite information from different columns in one column. Like before this query I did `SELECT a."ParcelID", a."PropertyAddress", b."ParcelID", b."PropertyAddress", COALESCE(b."PropertyAddress", a."PropertyAddress") FROM "NashvilleHousingData" a INNER JOIN "NashvilleHousingData" b ON a."ParcelID" = b."ParcelID" AND a."UniqueID" <> b."UniqueID" WHERE a."PropertyAddress" IS NULL;` and this work, but the next query gives me error – eliz Jul 11 '21 at 21:22
  • 1
    @eliz . . . You do not need an explicit `JOIN`. This does what you are trying to do. The `JOIN` is happening, but using the `WHERE` clause. – Gordon Linoff Jul 11 '21 at 21:41
  • I think you want `AND` instead of two `WHERE`. WHERE nhd2."ParcelID" = nhd."ParcelID" AND nhd2."UniqueID" <> nhd."UniqueID" WHERE nhd."PropertyAddress" IS NULL; – Andrew Lazarus Nov 04 '21 at 15:57