Sample Header Ad - 728x90

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