Often in PostgreSQL you may want to update the values in multiple columns at once.
You can use the following syntax to do so:
UPDATE athletes
SET points = 20, assists = 10
WHERE team = 'Hawks';
This particular example updates the values in both the points and assists columns of the athletes table to the following new values:
- points = 20
- assists = 10
Note that we use a WHERE statement in this example to only update the columns where the value in the team column is equal to Hawks.
The following example shows how to use this syntax in practice.
Example: How to Update Multiple Columns in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to update the values in the points and assists columns for each player who has a team name of Hawks.
We can use the following syntax to do so:
-- update values in points and assists columns
UPDATE athletes
SET points = 20, assists = 10
WHERE team = 'Hawks';
-- view updated table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
8 | Celtics | 14 | 13
2 | Hawks | 20 | 10
7 | Hawks | 20 | 10
9 | Hawks | 20 | 10
10 | Hawks | 20 | 10
From the output we can see that the values in the points and assists columns have been updated for each player on the Hawks team.
Note: Feel free to leave out the WHERE statement if you would instead like to update all rows in the table.
You can use similar syntax to update multiple columns at once in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values
