how can I update the record in this case?

I am storing the various activities with id's as 1,2,3,4 consecutively.

If I delete the 2nd recor开发者_开发问答d (id 2), how can I update so that 3 becomes 2 and 4 becomes 3?

Thanks in advance

update table set col1 = col1-1 where col1>2

Answer 1. You don't want to. The whole purpose of an id field is thet it is immutable.

Answer 2. You don't want to. If you need to do it, the id field isn't an id field at all, but a data field.

Answer 3. Delete the records and insert them again with the new numbers. You can see that this gets very expensive if the number of rows is big.

How about using a linked list approach instead, where each activity points to the next?

select * from activity;
+----+---------+
| id | next_id |
+----+---------+
|  1 |       2 |
|  2 |       3 |
|  3 |       4 |
|  4 |       5 |
|  5 |    NULL |
+----+---------+

If you want to delete the activity with ID=2, you need to update the row that points to ID=2 to the row that ID=2 previously pointed to.

update activity
   set next_id = 3
 where id = 1;

delete
  from activity 
 where id = 2;

+----+---------+
| id | next_id |
+----+---------+
|  1 |       3 |
|  3 |       4 |
|  4 |       5 |
|  5 |    NULL |
+----+---------+

If you have very long lists and are worried about DML performance, this may be a good option. The drawback of this method is that it is harder to query the table.