Often in PostgreSQL you may want to update only the first row that matches some condition.
You can use the following syntax to do so:
UPDATE athletes SET team='Mavericks'
WHERE id = (SELECT id FROM athletes WHERE team='Mavs' ORDER BY id LIMIT 1);
This particular example updates only the first row in the athletes table that contains ‘Mavs’ in the team column to be ‘Mavericks’ instead.
All other rows that contain ‘Mavs’ in the team column will be left unchanged.
The following example shows how to use this syntax in practice.
Example: How to Update Only First Matching Row 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
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+--------
1 | Celtics | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavs | 31
6 | Mavs | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Suppose that we would like to update only the first row in the table that contains ‘Mavs’ in the team column to be ‘Mavericks’ instead.
We can use the following syntax to do so:
-- update only first row with 'Mavs' in team column
UPDATE athletes SET team='Mavericks'
WHERE id = (SELECT id FROM athletes WHERE team='Mavs' ORDER BY id LIMIT 1);
-- view updated table
SELECT * FROM athletes ORDER BY id;
id | team | points
----+-----------+--------
1 | Celtics | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavericks | 31
6 | Mavs | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Notice that only the first row in the table that contains ‘Mavs’ in the team column has been updated to ‘Mavericks’ instead.
Feel free to use similar syntax to update only the first row that meets a condition in your own 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 Update Column with Column from Another Table
