How to store count value in DB [closed]

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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);