1

I have an application where I cannot change the way the app sorts the output. It wants to use a "sortorder" column no matter what.

I also have a large table where the sortorders are all currently zero. I want to populate those fields automatically, based on the alphabetical order of the "itemname" column.

The table is like this:

id     itemname     sortorder
1       A item         0
2       Big item       0
3       Cool item      0
4       Bad item       0

I want to run a query to make it be like this:

id     itemname     sortorder
1       A item         10
2       Big item       30
3       Cool item      40
4       Bad item       20

What's the best approach to do this?

Greg Holmes
  • 141
  • 13
  • What you use to consider a common item, a big item, a cool item and a bad item? You use exactly these names? – Minoru Sep 20 '13 at 13:50
  • Lucas, no :) I was just making up test data. Trying to be funny, I guess. (Not very successfully.) – Greg Holmes Sep 20 '13 at 14:21
  • But will you populate the `sortorder` based on every `itemname` or you have certains conditions to make it? – Minoru Sep 20 '13 at 14:24
  • @Lucas, no conditions. I just wanted the sortorder to match the existing alpha sort of the other column.I would just sort on the itemname if I could, but I have no ability to change the application that is displaying the data. Our numericly-named friend below has answered it. – Greg Holmes Sep 20 '13 at 14:38

1 Answers1

1
UPDATE TableName a
       INNER JOIN
      (
        SELECT  A.id, 
                A.itemName,
                @sort := @sort + 10 so
        FROM    TableName a,
                (SELECT @sort:=0) b
        ORDER   BY itemName, id
        ) b ON a.id = b.ID
SET a.sortorder = b.so
John Woo
  • 258,903
  • 69
  • 498
  • 492