Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table as below, order field is like auto increment, but it is not defined as auto_increment because i do not want any gaps in that value

    +--------------+
    |  id  | order1|
    +--------------+
    |  115 |     1 |
    |  116 |     2 |
    |  117 |     3 |
    |  118 |     1 |
    |  119 |     2 |
    |  120 |     6 |
    |  121 |     7 |
    |  122 |     8 |
    +--------------+

Like if i delete row with id 118 rows with id 119 should have order1 as 3 and rowId 120 should have 4 rowId 121 5

basically i want the order1 column be serial no with no gaps when there are any deletion

share|improve this question
3  
What is the question? Not having gaps in an autoincrement is silly: what happens to your history and audit trails for id 118 for exampkle? –  gbn May 24 '12 at 6:39
    
As i said it is not a Auto_increment field, did you read the very first line? –  Naveen Kumar May 24 '12 at 6:51
3  
Yes, I did. But why not? Rolling your own ID value generator isn't concurrency safe: you will get duplicates. –  gbn May 24 '12 at 6:53
1  
You will need to be espcially careful with this if you have related tables wespcially if you are so foolish as to not set up fromal PK/FK relationships. Renumbering without knowing what you are doing can cause massive data integrity problems in a poorly designed database. Can you tell us why you nhave arequirement for no gaps? Normally this is requirement I would push back and say no to due to the high chance of messing up the data unless it involved an actual legal reason why it was necessary. –  HLGEM May 24 '12 at 14:46
add comment

closed as not constructive by gbn, Aaron Bertrand, RolandoMySQLDBA, Marian, Paul White Oct 21 '12 at 22:37

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers

in your case you will have to repopulate the order column completely because if you delete row id 118 or 119, you need to change 118,119,120 and so on till the end of table

so in this case i suggest the following using the ROW_NUMBER() function

    update YourTable
    Set [order] = NewOrder
    from 
    (
    SELECT t.id,t.[order],@rownum := @rownum + 1 AS neworder
  FROM YourTable t, (SELECT @rownum := 0) r
    ) X
    where YourTable.id = X.id

so in this case if you delete 118

Delete from YourTable where id = 118

run the above update script again and then you will see that your order field is updated

Select id,[order] from YourTable
share|improve this answer
    
MySQL has not implemented window functions. –  ypercube May 24 '12 at 9:14
    
i changed it, its the same concept but apply mysql way. –  AmmarR May 24 '12 at 9:33
1  
UPDATE has different syntax in MySQL but the idea is surely correct. The internal subquery needs an ORDER BY order, too, to keep the current ordering. –  ypercube May 24 '12 at 9:34
    
thanks @ypercube –  AmmarR May 24 '12 at 9:54
add comment

I don't understand. If you delete order 118 you want to subtract 1 from all the orders that came later? If the id has no meaning at all except as a counter of orders that haven't been deleted, why bother storing anything and constantly having to update the entire table whenever a row changes? You can always generate this "order id" at run time and it is guaranteed to be accurate without relying on triggers or other background maintenance.

share|improve this answer
add comment

I used the below query to update the table after i delete something from the table

 update order1,(select @rownum:=0)dummy set order1 = 
(@rownum =@rownum+1);
share|improve this answer
1  
I have a multi-player game client at my employer's hosting company that does exactly this for ranking the top 10000 players. Hats off to @AmmaR !!! +1 for you and Ammar. –  RolandoMySQLDBA May 24 '12 at 15:22
    
@Rolando: Exactly like this? Without an ORDER BY? How do you guarantee what the final ordering is (how the values are assigned to order1 column)? –  ypercube May 25 '12 at 8:01
    
@ypercube the client used order by and limit 10000. I failed to mention that. –  RolandoMySQLDBA May 25 '12 at 8:37
add comment

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