'PostgreSQL update row based on the same table

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 != '""'
sqlpostgresql


Solution 1:[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 <> '""'
        ;

Solution 2:[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.

Solution 3:[3]

You can also try using subquery, for both updating from/to same table or different table.

 update table_name set column_name = (select column_name from table_name where id=13) where id=14;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Gordon Linoff
Solution 3 YasirAzgar

Related Questions