Ever wanted to perform an SQL update but needed to join on another table?
I recently learned how to do this, again, because I forgot the first time. I kept trying to use JOIN within the UPDATE and getting things either not working how I wanted or not working at all (which I guess are really the same thing fundamentally).
So I’m blogging here in case I forget again.
I try to think of it as ‘UPDATE uses FROM and WHERE like SELECT uses JOIN and ON’:
SELECT coulmn_name FROM table_1 JOIN table_2 ON table_2.table_1_id = table_1.id;
UPDATE table_1 SET coulmn_name FROM table_2 WHERE table_2.table_1_id = table_1.id;
Any other WHERE conditions for the UPDATE can just be added on the end.