Q:

Mysql query to reindex items

I have a table as follows, it has an ID and item name,order and type. 
  +--------+----------------+-----------+---------+
  |  ID    |  ITEM          |   ORDER   |  TYPE   |
  +--------+----------------+-----------+---------+
  |  1     |  Banana        |    2      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  2     |  Apple         |    1      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  3     |  Orange        |    4      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  4     |  Lemon         |    3      |  Fruit  |
  +--------+----------------+-----------+---------+

If I were to delete say Lemon the ORDER would be 1,2,4. Is there a method to reindex the remaining items to get the following result?
  +--------+----------------+-----------+---------+
  |  ID    |  ITEM          |   ORDER   |  TYPE   |
  +--------+----------------+-----------+---------+
  |  1     |  Banana        |    2      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  2     |  Apple         |    1      |  Fruit  |
  +--------+----------------+-----------+---------+
  |  3     |  Orange        |    3      |  Fruit  |
  +--------+----------------+-----------+---------+

A:

There's no way to do this completely automatically, but it would be fairly simple to do.  Assuming that ORDER is not unique it's easier, but if it is you would have to retrieve the ORDER of "Lemon" first.  Then, just do:
UPDATE Fruit SET ORDER = ORDER - 1 WHERE ORDER > ?

Where ? is Lemon's ORDER.
Assuming ORDER is not unique, then you could just do this query beforehand, but you would join on Fruit selecting Lemon's order to replace ?.

A:

Right before you delete the Lemon row - check what its ORDER column value is.
After that perform
UPDATE tbl SET `ORDER` = `ORDER` - 1 WHERE `ORDER` > value_lemon_had

