set a column with value from different table
Say that you have Table A:
- username
- email
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 tableB
SET email = tableA.Email
FROM tableA
WHERE tableB.username = tableA.username
AND 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
Last modified 1yr ago