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

Question

I have a table t1 with columns account_id and user_id, and another table users with columns id and account_id. I want to populate t1.account_id with the values of users.account_id.

For instance having

t1
user_id account_id
1 NULL
2 NULL

and

users
id account_id
1 10
2 11

I would like to populate t1.account_id with these values:

t1
user_id account_id
1 10
2 11

I've seen this answer for MySQL but can't have it working with PostgreSQL.

• Colin 't Hart: Did you try another answer to that same question? dba.stackexchange.com/a/21154/10361 should work for you.

Answer 1

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