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:

1. 10000
2. 5000
3. 1000

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.

This element:

(array[10000,5000,1000])[nextval('temp_seq')]

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;

Final effect:

# 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