Update multiple rows based on an ordered array

Question

I have a table which has a position pos column to indicate the order that the items should be displayed in the UI:

|               list_has_task              |
|------------------------------------------|
| list_id (fk) | task_id (fk) | pos | meta |
| ------------ | ------------ | --- | ---- |
|         6969 |         1001 |  1  | abcd |
|         6969 |         1002 |  2  | efgh |
|         6969 |         1003 |  3  | ijkl |
|         6969 |         1004 |  4  | mnop |
|         6969 |         1005 |  5  | qrst |
                     (...)

When the user rearranges the list I get an ordered list of IDs as an array:

(1004,1003,1002,1005,1001)

I want to update those rows to that order.
Here is what I have:

BEGIN;
   UPDATE list_has_task SET pos = 1 WHERE list_id = 6969 AND task_id = 1004;
   UPDATE list_has_task SET pos = 2 WHERE list_id = 6969 AND task_id = 1003;
   UPDATE list_has_task SET pos = 3 WHERE list_id = 6969 AND task_id = 1002;
   UPDATE list_has_task SET pos = 4 WHERE list_id = 6969 AND task_id = 1005;
   UPDATE list_has_task SET pos = 5 WHERE list_id = 6969 AND task_id = 1001;
COMMIT;

Is there any better way of doing this?

Answer 1

You can use a multi-table UPDATE with a VALUES table:

UPDATE list_has_task AS l
SET pos = a.pos
FROM (VALUES (1, 1004), (2, 1003), (3, 1002), (4, 1005), (5, 1001)) AS a(pos, task_id)
WHERE l.task_id = a.task_id

Output:

list_id     task_id     pos     meta
6969        1004        1       mnop
6969        1003        2       ijkl
6969        1002        3       efgh
6969        1005        4       qrst
6969        1001        5       abcd

Demo on SQLFiddle