CODEWITHSUNDEEP
phpmysql
qadenza
qadenza

Reputation: 9293

how to automatically set new id value as id of already deleted row

I have a table with 50 rows, id column as primary key, autoincrement.

At the beginning id values where from 1 - 50;

After deleting a row, for example id=1, and inserting a new row, this will have id=51.

How can I set the new row to take first empty place, it this case id=1 automatically, instead of 51;

Also if id=5 is deleted, the next inserted row should have id=5 an so on.

Upvotes: 0

Views: 337

Answers (2)

whatdaro
whatdaro

Reputation: 21

With autoincrement columns you can not change the keys checks per row.
You could disable key checks and the truncate the table which will reassign new keys(numbers) to each row, but that means previous keys will move to other rows depending on how many deleted rows and where they were in the sequence.

If you really need to do something like what you are doing, you could alternatively create a faux-indexed "auto" incremented column by your script. Then you would be able to manage that column in any manner that you chose to.

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30849

You can use MySQL's query variables to track the previous id and calculate the difference in the query, e,g.:

SELECT id, id - @previous AS difference, @previous := id
FROM test, (SELECT @previous := 0) a
ORDER BY id;

This would give you all the ids with difference. You can then wrap this into another query and SELECT the rows with difference > 1 to get the available id, e.g.:

SELECT (id - difference + 1) AS available_id
FROM (
 SELECT id, id - @previous AS difference, @previous := id
 FROM test, (SELECT @previous := 0) a
 ORDER BY id) b
WHERE b.difference > 1
LIMIT 1;

Here's the SQL Fiddle.

Upvotes: 1

Related Questions