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