Often in PostgreSQL you may want to update column values in one table with values from a column in another table.
You can use the following basic syntax to do so:
UPDATE athletes2 t2
SET team = t1.team_name
FROM athletes1 t1
WHERE t2.id = t1.id
AND t2.team IS DISTINCT FROM t1.team_name;
This particular example updates the values in the team column of the athletes2 table with the values from the team_name column of the athletes1 table.
The following example shows how to use this syntax in practice.
Example: Update Column Values with Column from Another Table in PostgreSQL
Suppose that we create the following table named athletes1 that contains information about points scored by basketball players on five different teams:
-- create table named 'athletes1'
CREATE TABLE athletes1 (
id INTEGER PRIMARY KEY,
team_name TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes1 VALUES (0001, 'Mavs', 22);
INSERT INTO athletes1 VALUES (0002, 'Spurs', 30);
INSERT INTO athletes1 VALUES (0003, 'Kings', 12);
INSERT INTO athletes1 VALUES (0004, 'Nuggets', 25);
INSERT INTO athletes1 VALUES (0005, 'Rockets', 31);
-- view all rows in table
SELECT * FROM athletes1;
id | team_name | points
----+-----------+--------
1 | Mavs | 22
2 | Spurs | 30
3 | Kings | 12
4 | Nuggets | 25
5 | Rockets | 31
And suppose that we create another table named athletes2 that contains information about points scored by basketball players on five other teams:
-- create table named 'athletes2'
CREATE TABLE athletes2 (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes2 VALUES (0001, 'Celtics', 35);
INSERT INTO athletes2 VALUES (0002, 'Heat', 22);
INSERT INTO athletes2 VALUES (0003, 'Knicks', 17);
INSERT INTO athletes2 VALUES (0004, 'Bulls', 14);
INSERT INTO athletes2 VALUES (0005, 'Magic', 30);
-- view all rows in table
SELECT * FROM athletes2;
id | team | points
----+---------+--------
1 | Celtics | 35
2 | Heat | 22
3 | Knicks | 17
4 | Bulls | 14
5 | Magic | 30
Suppose that we would like to update the values in the team column of the athletes2 table with all of the values from the team_name column of the athletes1 table.
We can use the following syntax to do so:
-- update values in 'team' column of 'athletes2' table
UPDATE athletes2 t2
SET team = t1.team_name
FROM athletes1 t1
WHERE t2.id = t1.id
AND t2.team IS DISTINCT FROM t1.team_name;
-- view updated rows in athletes2 table
SELECT * FROM athletes2;
id | team | points
----+---------+--------
1 | Mavs | 35
2 | Spurs | 22
3 | Kings | 17
4 | Nuggets | 14
5 | Rockets | 30
From the output we can see that the values in the team column of the athletes2 table have been updated with all of the values from the team_name column of the athletes1 table.
You can use similar syntax to update values in a column of your own table with values from a column in another table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Update Multiple Columns in Table
PostgreSQL: How to Update Each Row with Random Value
PostgreSQL: How to Create a Pivot Table
