I have a table for article that save the category for each article.
I want to update the sort order, but categorywise using a single query.
I am using this for data migration using bulk inserts and updated, so it looping the data is not a feasible solution.

id      category_id         sort_order
10          5                   1
11          5                   2
12          5                   3
13          6                   1
14          6                   2
15          6                   3
16          7                   1
17          8                   1
18          8                   2
19          7                   2
20          8                   3

Is it possible to do this in a single query?

I have written the query for update sequentially on all rows. But unable to do it categorywise. The query i have written is below

SET @so = 0;
UPDATE article A
INNER JOIN  category M ON A.category_id = M.id
SET A.sort_id = (@i := @i + 1)
WHERE A.user_id = 1;


  • Please provide CREATE TABLE and INSERT statements so that the problem can be easily reproduced – Lennart Mar 8 '18 at 12:39
  • @Lennart I have added the sqlfiddle. – Nandakumar V Mar 8 '18 at 13:34

This is rather tricky to do in MySQL compared to other DBMS:s. One way to try to overcome the limitations in MySQL, is to use user-defined variables. I must admit I find them almost counter-intuitive to use, so there may be errors in here:

update article a 
join (
    select case when @category <> category_id 
                then @rownum := 1 
                else @rownum := @rownum+1 
           end rownum, id
         , case when @category <> category_id 
                then @category := category_id 
    from article b
    cross join (select @category := -1) c
    order by b.category_id, b.id
) as r 
    on r.id = a.id  
set a.sort_order = r.rownum;

The idea here is that we traverse category according to order by b.category_id, b.id. We initialize @category to a category that does not exist (select @category := -1) c. Every time that category changes, we reset rownum to 1, if category is unchanged we increase rownum. The result:


I would probably look for other solutions if I were you. Ideas you may want to try is to use a cursor, I would even consider doing the update in a host language a viable option.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.