How to limit the amount of values within a postgres table which are updated
3
votes
1
answer
474
views
using the following sample:
CREATE TABLE tbl(
x integer,
y integer
)
INSERT INTO tbl
SELECT g AS x FROM generate_series(1, 10) AS g;
Which gives:
x | y
----+---
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
(10 rows)
I'd like to have something like (*note - I do not care about the ordering of the three values updated in y
here*):
x | y
----+---
1 | 1
2 | 2
3 | 3
4 |
5 |
6 |
7 |
8 |
9 |
10 |
(10 rows)
I've tried to do this using the following:
UPDATE tbl
SET y = x
WHERE y IS NULL
LIMIT 3;
But I get the error ERROR: syntax error at or near "LIMIT"
, which I'm not sure how to fix.
# Solution
I think the following works:
update tbl
set y = x
where x in (select x from tbl where y is null limit 3);
I can't think of a scenario this would fail, if there's a better approach it'd be good to know.
Asked by baxx
(326 rep)
Jan 4, 2023, 01:46 AM
Last activity: Jan 4, 2023, 01:46 PM
Last activity: Jan 4, 2023, 01:46 PM