CODEWITHSUNDEEP
phpmysqloracle-databasephpmyadminmysqli
Nisreen KH
Nisreen KH

Reputation: 90

How to delete and insert serial-num in MYSQL and also reorder serial-num?

===============
| id   | name |
===============
|    1 | Ali  |
|    2 | ahmd |
|    3 | jada |
|    4 | nisr |
|    5 | sara |
|    6 | mona |
|    7 | dana |
===============

I want to re-order ID when I delete any row like id=5 and be like that =>

===============
| id   | name |
===============
|    1 | Ali  |
|    2 | ahmd |
|    3 | jada |
|    4 | nisr |
|    5 | mona |
|    6 | dana |
===============

also the same this if I insert a row with id=2 name=yafa, how to re-order the table without duplicate any value and to be like that 

    | id   | name |
    ===============
    |    1 | Ali  |
    |    2 | yafa |
    |    3 | ahmd |
    |    4 | jada |
    |    5 | nisr |
    |    6 | sara |
    |    7 | mona |
    |    8 | dana |
    ===============

Upvotes: 0

Views: 207

Answers (1)

Vaibhav Desai
Vaibhav Desai

Reputation: 2728

This is assuming you always know what 'id' you are adding or deleting. (Also, you always work with only one row at a time).

For a delete:

DELETE FROM tableName WHERE id = 5;

UPDATE tableName 
SET    id = id-1
WHERE  id > 5;

For an insert:

UPDATE tableName 
SET    id = id + 1
WHERE  id >= 2;

INSERT INTO tableName (id, name) VALUES (2, 'yafa');

Btw, why would you want to do this. Set the column 'id' to be an auto-increment field and let the database deal with it. Do you care if you are skipping numbers ?

Upvotes: 1

Related Questions