0

There is a table "tbl" with unique values ​​"val" from a certain range (eg, from 100 to 999).

Need to choose the guaranteed value, which does not yet exist in the table without the aid of an additional table with all existing values ​​over the left association.

For example.

Helper table fill (whole range) with values from 1 to 9.

Basic table structure:

CREATE TABLE `ranger` (
  `val` int(2) unsigned NOT NULL,
  UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM;

INSERT INTO `ranger` (`val`) VALUES (1), (2), (4), (5), (6), (7), (8);

To select a non-existent value from ranger:

SELECT 
    val
FROM 
    ranger_helper
WHERE 
    val NOT IN(SELECT val FROM ranger)
ORDER BY 
    RAND() 
LIMIT 
    1
AEM
  • 1
  • 1
  • 2
    Not sure if I understood this correctly, but you want to find out, which values between 100 and 999 are not yet in `tbl.val`, is that it? – Lukas Eder May 12 '11 at 11:17
  • In MySQL it would be more efficient to do a `LEFT JOIN` on ranger and look for `NULL` columns in the RH table than this subselect. – James C May 12 '11 at 11:32

2 Answers2

0
SELECT id FROM myTable WHERE id NOT IN (100,101,102,103...,999);

could work if you can derive the range easily externally

Another approach could be to create a stored procedure and either derive the range with a WHILE loop or query for each value in the table.

James C
  • 14,047
  • 1
  • 34
  • 43
  • 1
    I added a clarification. The problem is that the range is very large from 100000000 to 999999999. – AEM May 12 '11 at 11:33
0

There is no number generator in MySQL. See this question and answer:

How do I make a row generator in MySQL?

At the very best, you can derive it from the range using an anti-join:

select id from foo where id not in (...);
Community
  • 1
  • 1
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154