PostgreSQL update row based on the same table

Question

Hi I would update row based on the same table. Copy column "data" to every row where data is "" (empty). "Key" in this rows is the same.

    id |data |key 
   ----|-----|-----
    1  | xyz |key1
   ----|-----|-----
    2  | ""  |key1

I've tried something like that, but "relation a does not exists":

UPDATE a
SET a.data = b.data
FROM table a
  INNER JOIN table b
    ON (a.key = b.key)
WHERE b.data != '""'

Answer 1

In Postgres SQL, you should not repeat the name of the target table in the FROM clause (so you cannot use a JOIN)

---

UPDATE table_a dst   -- <<-- target table
SET data = src.data  -- <<-- no table-alias on the lValue; correlation name is implicit here
FROM table_a src     -- <<--- same table, different alias
WHERE dst.key = src.key
AND dst.data = '""'
AND src.data <> '""'
        ;

Answer 2

In Postgres, the syntax is:

UPDATE a
    SET a.data = b.data
    FROM a b
    WHERE a.key = b.key AND b.data <> '""' AND a.data = '""';

Note: This assumes that a is the table name; b is then an alias for the table and the "join" conditions are in the WHERE clause.