Often in PostgreSQL you may want to swap the values of two columns.
You can use the UPDATE statement with the following syntax to do so:
UPDATE athletes
SET points = assists,
assists = points;
This particular example will swap the values of the points and assists columns in the athletes table.
If you’d like, you can also use a WHERE clause to only swap the values in rows that meet a certain condition:
UPDATE athletes
SET points = assists,
assists = points
WHERE team = 'Mavs';
This particular example will swap the values of the points and assists columns only where the value in the team column is equal to ‘Mavs’.
The following example shows how to use this syntax in practice.
Example: How to Swap Column Values 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 (
id 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;
id | 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
We can use the following syntax to swap the values in the points and assists columns:
-- swap values in points and assists columns
UPDATE athletes
SET points = assists,
assists = points;
-- view updated rows in table
SELECT * FROM athletes;
id | team | points | assists
----+---------+--------+---------
1 | Mavs | 5 | 22
2 | Hawks | 12 | 30
3 | Kings | 3 | 12
4 | Kings | 9 | 25
5 | Mavs | 14 | 31
6 | Mavs | 3 | 16
7 | Hawks | 5 | 10
8 | Celtics | 13 | 14
9 | Hawks | 8 | 20
10 | Hawks | 3 | 26
We can see that the values in the points and assists columns have been swapped for each row in the table.
If we’d like, we can use the following syntax to only swap the values in the points and assists columns where the team name is ‘Mavs’:
-- swap values in points and assists columns where team = 'Mavs'
UPDATE athletes
SET points = assists,
assists = points
WHERE team = 'Mavs';
-- view updated rows in table
SELECT * FROM athletes;
id | team | points | assists
----+---------+--------+---------
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
1 | Mavs | 5 | 22
5 | Mavs | 14 | 31
6 | Mavs | 3 | 16
We can see that the values in the points and assists columns have been swapped only for the rows where the team name is ‘Mavs’.
Feel free to use similar syntax to swap column values in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Columns by Position
PostgreSQL: How to Select Columns Based on Condition
PostgreSQL: How to Count Occurrences of Each Value in Column
