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?

score 3 · Answer 1 · answered Apr 09 '20 at 00:26

3

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

answered Apr 09 '20 at 00:26

Nick

138,499
22
57
95

This is exactly what I need! – Vitim.us Apr 09 '20 at 01:05
Also I didn't know SQLFiddle existed! tysm – Vitim.us Apr 09 '20 at 01:06
1

@Vitim.us yeah, SQLFiddle is good (I like its text to ddl feature), but there's also https://dbfiddle.uk which has a bigger range of DBMS available and https://db-fiddle.com which also has text to ddl – Nick Apr 09 '20 at 01:08
In MySQL, (VALUES ROW(1, 1004), ROW(2, 1003)) should work. Just need to add ROW keyword before each tuple. – Anutrix Sep 16 '22 at 22:27

Erwin Brandstetter · Answer 2 · 2020-04-09T01:52:08.887

1

I get an ordered list of IDs as an array.

You can use this directly, unnest() and make use of the WITH ORDINALITY feature:

UPDATE list_has_task l
SET    pos = x.ord
FROM   unnest('{1004,1003,1002,1005,1001}'::int[]) WITH ORDINALITY x(task_id, ord)
WHERE  l.list_id = 6969
AND    l.task_id = x.task_id;

db<>fiddle here

See:

PostgreSQL unnest() with element number

edited Apr 09 '20 at 01:52

answered Apr 09 '20 at 01:38

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 3 · answered Sep 16 '22 at 23:08

0

In MySQL, it's a bit different:

UPDATE
  real_table AS rt,
  (
    VALUES
      ROW(1, 1004),
      ROW(2, 1003),
      ROW(3, 1002),
      ROW(4, 1005),
      ROW(5, 1001)
  ) AS ft (pos, task_id)
SET
  rt.pos = ft.pos
WHERE
  rt.task_id = ft.task_id

answered Sep 16 '22 at 23:08

Anutrix

145
1
9

The question doesn't mention MySQL, it's only tagged with PostgreSQL. – Nick Sep 17 '22 at 00:57

Update multiple rows based on an ordered array

3 Answers3