[thelist] SQL Question: "Fill in" missing auto-increment records
David Bindel
dbindel at austin.rr.com
Sat May 25 22:20:00 CDT 2002
I am creating a user registration MyISAM database in MySQL that has an
auto-incremented ID field.
Example (original):
ID | Name
-------------------------
1 | Thomas Jefferson
2 | George Washington
3 | George Bush
4 | Abraham Lincoln
.......
Periodically (about once a month), I will run a script that removes user
records that have not been used in the previous month (to keep size down and
accuracy up.)
Example (after clean-up script):
ID | Name
-------------------------
1 | Thomas Jefferson
3 | George Bush
Now because the ID field is auto_incremented, the next user to submit their
registration will have their ID set to 5, even though the previous ID number
is 3.
Example (next user added):
ID | Name
-------------------------
1 | Thomas Jefferson
3 | George Bush
5 | Bill Clinton
What I am wanting to do is have some kind of SQL query that finds the
"missing" ID numbers and puts the next user record in its spot instead of
assigning it the next auto_incremented ID number.
Example (next user added, after the SQL query which I hope exists):
ID | Name
-------------------------
1 | Thomas Jefferson
2 | Ronald Reagan
3 | George Bush
5 | Bill Clinton
Does anyone know how to do this?
Thanks in advance,
David Bindel
More information about the thelist
mailing list