Say that you have Table A:
username
And Table B:
item_id
username
You want to add email to table B
where the value taken from table A
. After adding a column on Table B, this query can be used:
UPDATE tableBSET email = tableA.EmailFROM tableAWHERE tableB.username = tableA.usernameAND tableB.email IS DISTINCT FROM tableA.email; -- optional, see below​
The final WHERE
clause prevents updates that wouldn't change anything - which is practically always a good idea (almost full cost but no gain, exotic exceptions apply). If both old and new value are guaranteed to be NOT NULL
, simplify to:
AND tableB.email <> tableA.email
Taken from: https://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql​