How to update records of one table with the values of another one (PostgreSQL)
update documentation

UPDATE t1 AS a SET
  account_id = b.account_id
FROM users b
WHERE a.user_id = b.id;


The key is that you need to make sure you have a link between the table you're updating (aliased as "a" in my example) and the table you're using .. which is done via the WHERE clause... a.user_id = b.id