Let's imagine following situation:
create table test (id int4 primary key, priority int4); insert into test (id) select distinct (random() * 100000000)::int4 from generate_series(1,1000);
Table test will now contain some (up to 1000) records, with random ids.
Now, we want to update first 3 records (ordered by id) to have following values in priority:
Now, I could of course do something like this:
# select id from test order by id asc limit 3;
And then issue multiple updates (3 in this example), but there has to be a way to do it in less roundtrips, and additional logic on application side.
Well, I found this way:
First, I need to get first three rows, which is simple:
select id from test order by id asc limit 3.
Then I will have to somehow “attach" to them the priority values. I found a relatively simple way using temporary sequence:
create temp sequence temp_seq; select id, (array[10000,5000,1000])[nextval('temp_seq')] from ( select id from test order by id asc limit 3 ) as x;
Whoa, what does it do?
It's rather simple: it gets first 3 records using the query we had before, then (using subselect to preserve ordering) gets next value for given row.
Generates array with 3 items – values 10000, 5000 and 1000, and then chooses only one of the values – the one with index given by nextval().
Since nextval() gives values in order: 1, 2, 3, …, and array indexes start with 1 – it's perfect match.
Now, all that is left is to update the table, but since we used first 3 values from seq, I will just recreate it, and issue the update:
drop sequence temp_seq; create temp sequence temp_seq; update test as t SET priority = q.priority FROM ( select id, (array[10000,5000,1000])[nextval('temp_seq')] as priority from ( select id from test order by id asc limit 3 ) as x) as q where t.id = q.id;
# select * from test order by id asc limit 4; id | priority --------+---------- 187272 | 10000 219755 | 5000 244572 | 1000 457438 | [null] (4 rows)
Done. The code is not really pretty, but it does the job, and it requires only 2 commands to be send:
- creation of temporary sequence
- update itself