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