Joining one table twice in postgresql

Question

I have two columns in the same table that I want to join in Postgresql but for some reason I’m getting this error. Don’t know how to figure it out. Please help.

[42P01] ERROR: relation "a" does not exist
Position: 10

X table contains two pools(ABC,XYZ), ids, numbers and description. If an ID exists in one pool but not in the other, it should update description column to “ADD”. Pools need to be joined on number.

UPDATE     A
SET        A.Description = 'ADD'
FROM       X  AS A
LEFT JOIN  X  AS B ON B.number = A.number
          AND B.id = 'ABC'
WHERE      A.id = 'XYZ'
AND       B.number IS NULL
AND       A.Description IS NULL;

"_Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid._" [Link](https://www.postgresql.org/docs/current/static/sql-update.html) — Abelisto, Jul 25 '18 at 14:12

score 0 · Answer 1 · answered Jul 25 '18 at 00:46

With standard SQL you can't do a join as part of an update, but what you can do is include a subquery to select the id's to update. The subquery can contain a join. I'm not entirely clear on what you're actually trying to accomplish, but you could do something like this:

UPDATE x SET description='ADD' WHERE number IN (
    SELECT a.number FROM x AS a
    LEFT OUTER JOIN x AS b ON a.number=b.number AND a.id='XYZ' AND b.id='ABC' 
    WHERE b.number IS NULL
);

This will join the table x with itself and will select (and update) any numbers's that don't have a matching number in the 'ABC' and 'XYZ' zone.

PostgreSQL does have a UPDATE FROM syntax that does let you update with complex subqueries. It's more flexible but it's non-standard. You can find an example of this type of query here.

Joining one table twice in postgresql

1 Answers1