Update categorywise in MySql 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;


SQLFIDDLE
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 &lt;&gt; category_id 
                then @rownum := 1 
                else @rownum := @rownum+1 
           end rownum, id
         , case when @category &lt;&gt; category_id 
                then @category := category_id 
           end
    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:

http://sqlfiddle.com/#!9/aa754b/1/0

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.