CODEWITHSUNDEEP
mysqlsqldatabaseselectsql-order-by
Kasper Jan Mooijman
Kasper Jan Mooijman

Reputation: 186

Reorder a MYSQL table

I have a MySql table with a 'Order' field but when a record gets deleted a gap appears how can i update my 'Order' field sequentially ? If possible in one query 1 1

id.........order
1...........1
5...........2
4...........4
3...........6
5...........8

to 

id.........order
1...........1
5...........2
4...........3
3...........4
5...........5

I could do this record by record

Getting a SELECT orderd by Order and row by row changing the Order field but to be honest i don't like it.

thanks

Extra info :

I also would like to change it this way : 

id.........order
1...........1
5...........2
4...........3
3...........3.5
5...........4

to 

id.........order
1...........1
5...........2
4...........3
3...........4
5...........5

Upvotes: 0

Views: 1938

Answers (3)

Techmag
Techmag

Reputation: 1391

The above answer is excellent but it took me a while to grok it so I offer a slight rewrite which I hope brings clarity to others faster: 

update 
   originalTable 
   join (select originalTable.ID, 
      (@newValue := @newValue + 10) as newValue 
   from originalTable 
   cross join (select @newValue := 0) newTable
   order by originalTable.Sequence) 
   originalTable_reordered 
   on originalTable.ID = originalTable_reordered.ID 
set originalTable.Sequence = originalTable_reordered.newValue;

Note that originalTable.* is NOT required - only the field used for the final join.

My example assumes the field to be updated is called Sequence (perhaps clearer in intent than order but mainly sidesteps the reserved keyword issue)

What took me a while to get was that "const" in the original answer was not a MySQL keyword. (I'm never a fan of abbreviations for that reason -- the can be interpreted many ways at times especially at these very when it is best they not be misinterpreted. Makes for verbose code I know but clarity always trumps convenience in my books.)

Not quite sure what the select @newValue := 0 is for but I think this is a side effect of having to express a variable before it can be used later on.

The value of this update is of course an atomic update to all the rows in question rather than doing a data pull and updating single rows one by one pragmatically.

My next question, which should not be difficult to ascertain, but I've learned that SQL can be a trick beast at the best of times, is to see if this can be safely done on a subset of data. (Where some originalTable.parentID is a set value).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In MySQL you can do this:

update t join
       (select t.*, (@rn := @rn + 1) as rn
        from t cross join
             (select @rn := 0) const
        order by t.`order`
       ) torder
       on t.id = torder.id
    set `order` = torder.rn;

In most databases, you can also do this with a correlated subquery. But this might be a problem in MySQL because it doesn't allow the table being updated as a subquery:

update t
    set `order` = (select count(*)
                   from t t2
                   where t2.`order` < t.`order` or
                         (t2.`order` = t.`order` and t2.id <= t.id)
                 );

Upvotes: 4

LJ Wilson
LJ Wilson

Reputation: 14427

There is no need to re-number or re-order. The table just gives you all your data. If you need it presented a certain way, that is the job of a query.

You don't even need to change the order value in the query either, just do:

SELECT * FROM MyTable WHERE mycolumn = 'MyCondition' ORDER BY order;

Upvotes: 0

Related Questions