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.

score 2 · Accepted Answer · answered Feb 18 '15 at 22:25

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

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

1 Answers1