CODEWITHSUNDEEP
mysqltriggerssequence
Mitchell Bennett
Mitchell Bennett

Reputation: 27

Keeping MySQL values in sequence after deletion

Suppose I have the following table called "Test" in a MySQL database (where id is the primary key):

id | entry |
---+-------+
 1 |  a    | 
 2 |  b    | 
 3 |  c    | 
 4 |  c    | 
 5 |  d    | 
 6 |  e    |

What I would like to know is how can I keep the id values in the sequence {1, 2, 3, ..., n-1, n} after deleting a row from the table. For example, say I execute the following:

 DELETE FROM Test WHERE id = 4;

Then I would like the table to look like this after deletion:

 id | entry |
 ---+-------+
  1 |  a    | 
  2 |  b    | 
  3 |  c    | 
  4 |  d    | 
  5 |  e    |

I think the solution is to use a database trigger but I'm not too sure how to create a trigger which implements this behaviour.

Upvotes: 0

Views: 241

Answers (1)

Michael
Michael

Reputation: 12806

You could run this query:

SET @id = 0;
UPDATE `Test` SET `id` = @id:= @id + 1;

Although I don't know why you'd want to...

Upvotes: 2

Related Questions