CODEWITHSUNDEEP
sqlpostgresql
James Cook
James Cook

Reputation: 389

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

Upvotes: 12

Views: 12239

Answers (3)

YasirAzgar
YasirAzgar

Reputation: 1453

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;

Upvotes: 0

joop
joop

Reputation: 4503

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 <> '""'
        ;

Upvotes: 22

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 5

Related Questions