CODEWITHSUNDEEP
sqlmysqlsequence
Vijer
Vijer

Reputation: 11

How to re-sequence an id column after deleting a row (NOT AN AUTO-INCREMENT)

I have a table 'order_item' with order_item_id int order_id int

the primary key is order_item_id + order_id

So the order_item_id is not unique and neither is the order_id, but the combination of the two columns is unique.

If a customer adds multiple items to their order we could have a table like this

order_item_id   order_id
------------------------
1                5
2                5
3                5
4                5

...and if the customer then deletes order_item_id 2 then there is a gap in the sequence. The order_item_id is displayed for the customers reference and used when we send purchase orders to vendors.

Is there a way to re-sequence the order_item_id for a specific order_id? I have tried the following but keep getting a duplicate key error and it keeps changing the first found item id to 0 not 1.

SET @i := 0;

  UPDATE order_item
     SET order_item_id = (@i := @i + 1)
   WHERE order_id = 5
ORDER BY order_id, order_item_id

Thanks for any assistance.

Upvotes: 1

Views: 4007

Answers (4)

Vijer
Vijer

Reputation: 11

Actually the modified code using hartihski's suggestions works, but there must be a problem with SQLyog executing the query. When I used phpMyAdmin to execute the query, it worked as expected.

Upvotes: 0

user330315
user330315

Reputation:

To add to newtover's comment:

Changing the primary key of a row only for displaying purposes is a very bad idea. You can always generate the "row number" on the fly on the client side.

If you need to do that on the database side, you can do that during retrieval. No need to change the primary key.

Unfortunately MySQL does not support the row_number() function, but this can be worked around using the following "hack":

SELECT @rownum := @rownum + 1 as order_item_sequence, 
       o.order_item_id,
       o.order_id,
FROM order_table o,
  (SELECT @rownum := 0) r
WHERE o.order_id = 42;

Upvotes: 1

newtover
newtover

Reputation: 32094

Just do not confuse ids and numbers in ordering, and you will not have the problem. The latter can easily be done even on client side. It is a very bad idea to change primary keys.

Upvotes: 2

harithski
harithski

Reputation: 686

You can do it only for those order_item_ids that are greater than the one that just has been deleted.

First set @i := deleted_order_item_id - 1;

And add a where clause something like where order_item_id > deleted_order_item_id and increment using order_item_id = (@i := @i + 1)

Upvotes: 0

Related Questions