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?

Nick · Accepted Answer · 2020-04-09 00:26:16Z

4

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 9, 2020 at 0:26

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Victor Over a year ago

This is exactly what I need!

Victor Over a year ago

Also I didn't know SQLFiddle existed! tysm

Nick Over a year ago

@Vitim.us yeah, SQLFiddle is good (I like its text to ddl feature), but there's also dbfiddle.uk which has a bigger range of DBMS available and db-fiddle.com which also has text to ddl

Anutrix Over a year ago

In MySQL, (VALUES ROW(1, 1004), ROW(2, 1003)) should work. Just need to add ROW keyword before each tuple.

Erwin Brandstetter · Accepted Answer · 2020-04-09 01:52:08Z

2

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 9, 2020 at 1:52

answered Apr 9, 2020 at 1:38

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Comments

Anutrix · Accepted Answer · 2022-09-16 23:08:22Z

1

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, 2022 at 23:08

Anutrix

2922 silver badges15 bronze badges

1 Comment

Nick Over a year ago

The question doesn't mention MySQL, it's only tagged with PostgreSQL.

Collectives™ on Stack Overflow

Update multiple rows based on an ordered array

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related