CODEWITHSUNDEEP
mysqlindexing
H. Ferrence
H. Ferrence

Reputation: 8116

SQL Query to List Unused Numerical IDs

I have a table indexed on column=ID and it is a 5-digit integer.

It is a very old table and was never auto incremented.

I want to list all unused IDs.

I can do it with a SELECT * ORDER BY statement and then run the result against an incremented for() loop, but I was wondering if anyone knows if a SQL Query Statement to do this within MySQL or if you have a more clever method than the approach I am planning to take.

Thanks

Upvotes: 0

Views: 596

Answers (3)

Mik
Mik

Reputation: 1703

i know this works with inserts ... maybe also works with updates.... would test it but im on a windows pc right now and no mysql installed :)

select @i := 1;
update tbl set id = @id:=@id+1;

http://www.electrictoolbox.com/autoincrement-value-mysql/

UPDATE:

its tested now and works very good... start with 

select @i := 0;

so that your first entry in the db starts with 1;

Upvotes: 0

Marc
Marc

Reputation: 16512

You could do a query like this

    SELECT id+1 as startId, (SELECT MIN(t3.id) -1 FROM table t3 
       WHERE t3.id > t1.id) as endId
    FROM Table AS t1
    WHERE 
    NOT Exists 
        (
           SELECT t2.id
           FROM Table as t2
           WHERE t2.id+1 = t2.id
        )
    HAVING (SELECT MIN(t3.id) -1 FROM table t3 
       WHERE t3.id > t1.id) IS NOT NULL

Upvotes: 0

Kermit
Kermit

Reputation: 34063

This is the approach I would take.

  1. Create a number table going from 1 to n (n being your largest ID). Numbers can be generated using a query such as this.
  2. Perform a LEFT JOIN to the number table and fetch only NULL results.

See this demo

Upvotes: 1

Related Questions