title: How to re-sequence an id column after deleting a row (NOT AN AUTO-INCREMENT)
body: <p>I have a table 'order_item' with 
  order_item_id int
  order_id int</p>
  
  <p>the primary key is order_item_id + order_id</p>
  
  <p>So the order_item_id is not unique and neither is the order_id, but the combination of the two columns is unique.</p>
  
  <p>If a customer adds multiple items to their order we could have a table like this</p>
  
  <pre><code>order_item_id   order_id
  ------------------------
  1                5
  2                5
  3                5
  4                5
  </code></pre>
  
  <p>...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.  </p>
  
  <p>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.</p>
  
  <pre><code>SET @i := 0;
  
    UPDATE order_item
       SET order_item_id = (@i := @i + 1)
     WHERE order_id = 5
  ORDER BY order_id, order_item_id
  </code></pre>
  
  <p>Thanks for any assistance.</p>