Problem in PostgreSQL (ERROR: relation "a" does not exist)

Question

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

Does this answer your question? updating table rows in postgres using subquery — astentx
– astentx, Commented Jul 11, 2021 at 22:49

Gordon Linoff · Accepted Answer · 2021-07-11 19:27:43Z

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"

answered Jul 11, 2021 at 19:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eliz Over a year ago

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

Gordon Linoff Over a year ago

@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.

Andrew Lazarus Over a year ago

I think you want AND instead of two WHERE. WHERE nhd2."ParcelID" = nhd."ParcelID" AND nhd2."UniqueID" <> nhd."UniqueID" WHERE nhd."PropertyAddress" IS NULL;

Collectives™ on Stack Overflow

Problem in PostgreSQL (ERROR: relation "a" does not exist)

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related