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.

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.

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;  

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)

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.