MYsql Update sequence number in order column for multiple rows

Question

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.

You're also asking us to provide SQL code to replace a process the front end is doing, but you didn't tell us what the front end is actually doing so your question can't really be answered.. — Caius Jard
– Caius Jard, Commented May 11, 2021 at 9:02

Akina · Accepted Answer · 2021-05-11 06:45:40Z

2

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.

edited May 11, 2021 at 6:45

answered May 11, 2021 at 6:39

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

6 Comments

Caius Jard Over a year ago

But it doesnt really answer the question Is there any alternative way to do it in Mysql so i can avoid looping because the looping still used to build the set string?

Akina Over a year ago

@CaiusJard IMPLODE function usage is not "looping".

Tim Biegeleisen Over a year ago

Having the need to use FIND_IN_SET in this way often indicates a code smell.

Caius Jard Over a year ago

@Akina we don't know what the front end is doing but we can assume looping because the OP says that's what they want to avoid. Sure aint MySQL doing any looping with a CASE, so... I maintain my belief that the OP has asked the wrong question, or an XY question; they made the decision to solve something in SQL, but it's probably not SQL's job to solve it

Akina Over a year ago

@CaiusJard OP tells that "Currently im looping the ids in PHP to generate the "case when" statement". As I understand he have an array where $ar[0]=13, $ar[1]=6, .. and so on, and he loops over this array for to add keywords WHEN and THEN and to combine the array into one string to be inserted into the query text. Maybe I'm wrong... I simply show another form of provided query which does not contain detailed CASE.

|

Tim Biegeleisen · Accepted Answer · 2021-05-11 06:24:30Z

0

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);

answered May 11, 2021 at 6:24

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

10 Comments

Strawberry Over a year ago

I'm struggling to see the advantage of a separate table

Tim Biegeleisen Over a year ago

I'm struggling to not see the advantage of a separate table, unless the handful of values in the CASE expression are one-offs, and would never be expected to change.

Strawberry Over a year ago

Nope, you've lost me

Tim Biegeleisen Over a year ago

Relational information, such as mapping id to something else, generally belongs in a table, not in the query itself. The WHERE clause I have might even be optional, if the OP wants to do a blanket mapping of all id values.

Strawberry Over a year ago

But aren't you just moving the CASE query to the mappings table? Generally, mappings are only required where the relationship is not 1 to 1.

|

Luca · Accepted Answer · 2021-05-11 09:39:59Z

0

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);

answered May 11, 2021 at 9:39

Luca

568 bronze badges

Collectives™ on Stack Overflow

MYsql Update sequence number in order column for multiple rows

3 Answers 3

6 Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related