CODEWITHSUNDEEP
mysql
user3063952
user3063952

Reputation: 97

MySQL query of available values

I have the table test:

cod double  PRI     
nom varchar(100)
tip varchar(50) 
des varchar(500)
pre double

The table contains a numeric primary key called "cod". The cod values go from 1000 to 40000.

The rows can be deleted. F/E:

Having cod:

1001 X X X 1.0
1002 Y Y Y 2.0
1003 Z Z Z 3.0
1004 A A A 4.0
1005 B B B 5.0

and if 1002 is deleted, there's a missing number between 1001 and 1003. 

1001 X X X 1.0
1003 Z Z Z 3.0
1004 A A A 4.0
1005 B B B 5.0

Can I do a query to get all these missing numbers so I can suggest the user what cod values are free to register new rows?

Upvotes: 0

Views: 49

Answers (2)

Anthony Raymond
Anthony Raymond

Reputation: 7872

You can use this :

SELECt test1.cod - 1 AS missing_cod
FROM test AS test1
LEFT JOIN test test2 ON test2.cod = test1.cod - 1
WHERE test2.cod IS NULL

this should work (i think), i don't know what is going to happen if you got more than one missing number at time

Why are you using double with integer value on cod column?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest a different approach. If you have a table that is limited to 39000 rows, then create that table with all the rows to begin with.

Then, have an IsAvailable flag. Instead of deleting records, you can just update the flag:

update test t
    set IsAvailable = 1
    where cod = < whatever >;

When you want to use a value, then set the available flag to 1.

Then, you can get the first 10 available slots using something like:

select cod
from test
where IsAvailable = 1
limit 10;

By the way, you refer to cod as an integer throughout the question, yet it is declared as a double. I strongly suggest you make the column an integer value.

Upvotes: 1

Related Questions