CODEWITHSUNDEEP
mysql
sethuraman
sethuraman

Reputation: 189

MYsql Update sequence number in order column for multiple rows

I need to update the display_order column with sequence for numbers for array of ids Consider the following array of Ids (13, 6, 5, 19, 1, 3, 2), for this ids i need to update the order column

DB

+----+-------+---------------+
| id | name  | display_order |
+----+-------+---------------+
| 1  | cat1  | 3             |
+----+-------+---------------+
| 2  | cat2  | 4             |
+----+-------+---------------+
| 5  | cat5  | 6             |
+----+-------+---------------+
| 6  | cat6  | 1             |
+----+-------+---------------+
| 13 | cat13 | 2             |
+----+-------+---------------+

Currently im using the following query

UPDATE categories
   SET display_order = CASE id 
                      WHEN 13 THEN 1 
                      WHEN 6 THEN 2 
                      WHEN 5 THEN 3 
                      WHEN 19 THEN 4 
                      WHEN 1 THEN 5 
                      WHEN 3 THEN 6 
                      WHEN 2 THEN 7 
                      
                      END
 WHERE id IN (13, 6, 5, 19, 1, 3, 2);

Currently im looping the ids in PHP to generate the "case when" statement, the array may come with more Ids. Is there any alternative way to do it in Mysql so i can avoid looping.

Upvotes: 0

Views: 916

Answers (3)

Luca
Luca

Reputation: 56

You can use insert into on duplicate key update.

for example:

INSERT INTO categories(id, display_order)
VALUES(13,1),
(6,2),
(5,3),
(19,4),
(1,5),
(3,6),
(2,7) 
ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    display_order = VALUES(display_order);

Upvotes: 0

Akina
Akina

Reputation: 42612

UPDATE categories
SET display_order = FIND_IN_SET(id, '13,6,5,19,1,3,2')
WHERE id IN (13, 6, 5, 19, 1, 3, 2);

Pay attention - the ids list in FIND_IN_SET is one string literal without spaces after commas. id is converted to string before searching, and the searching is performed as textual one.

If you want to transfer ids list into the query once you may use the next form:

UPDATE categories
CROSS JOIN ( SELECT '13,6,5,19,1,3,2' ids ) ids 
SET categories.display_order = FIND_IN_SET(categories.id, ids.ids)
WHERE FIND_IN_SET(categories.id, ids.ids);

but it will be slower due to textual comparing (and hence full table scan - no indices can be used for this query). Do it on compact (less than ~1000 rows) table only.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You should maintain the id values and their mappings in a separate table display_orders:

id | val
1  | 5
2  | 7
3  | 6
5  | 3
6  | 2
13 | 1
19 | 4

Then, just do an update inner join:

UPDATE categories c
INNER JOIN display_orders d
    ON d.id = c.id
SET
    display_order = d.val
WHERE
    c.id IN (1, 2, 3, 5, 6, 13, 19);

Upvotes: 0

Related Questions