0

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

Kermit
  • 33,827
  • 13
  • 85
  • 121
H. Ferrence
  • 7,906
  • 31
  • 98
  • 161
  • What is your DBMS? You shouldn't ever `SELECT *`. Always specify a column list. – Kermit Nov 28 '12 at 20:21
  • Do you have any other table with autoincremented id field from 1 up to 10000? – Andrew Logvinov Nov 28 '12 at 20:22
  • I am not sure I understand the reason for your question @AndrewLogvinov -- but the answer is no – H. Ferrence Nov 28 '12 at 20:23
  • 1
    The nature is that if you have such a table you can use `select id from table1 where id not in (select id from table2)`. – Andrew Logvinov Nov 28 '12 at 20:24
  • Don't miss the point of my question @njk -- I really never do a SELECT * -- I am merely here to see if someone has a clever solution. I can solve this easily in the fashion I mention, but I am always looking to learn new and more creative methods for doing things.,that's all. – H. Ferrence Nov 28 '12 at 20:26
  • Ah interesting approach @AndrewLogvinov -- I'll think about that solution – H. Ferrence Nov 28 '12 at 20:27

3 Answers3

1

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

Community
  • 1
  • 1
Kermit
  • 33,827
  • 13
  • 85
  • 121
  • Alternatively to creating a numbers table, you can create a [generator view](http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql). – PinnyM Nov 28 '12 at 20:27
  • Yes, @njk that is the same thing that AndrewLogvinov suggested above. Looks like you both have the best solution. – H. Ferrence Nov 28 '12 at 20:28
  • @H.Ferrence I've added a demo for you. Also, Andrew is suggesting to use an existing table that may have a continuous auto-incremented id. – Kermit Nov 28 '12 at 20:35
0

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
Marc
  • 16,170
  • 20
  • 76
  • 119
0

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;

Mik
  • 1,705
  • 1
  • 14
  • 26
  • what i understood is that you want to cleanup your table... means reindex all your entries starting with the first until the last one... the above will do exactly that ... but i think you will have to tell your mysql the highest id of your table... otherwise it will start autoincrementing the last id you had before – Mik Nov 29 '12 at 10:33