There is a table, the id in which is filled manually. It turned out that some id were missing. I can get a list of id 's in the table. How do I use sql to calculate the id , which is not in this list?

  • Build a sequence and compare with current IDs, sequence building commands may be missing, it depends on the manufacturer and version of the database server - 2SRTVF
  • Specify the DBMS. Without this, only general advice is possible. Also specify the desired output form - one id per record, or better "from-to". - Akina
  • Completed. The range is from 1 to 2500. From this range you need to get a list of missing id. - Bakhuss
  • what version of mysql? - Novitskiy Denis
  • SHOW VARIABLES command issues: version 10.1.34-MariaDB. - Bakhuss

2 answers 2

At hand, there is no database of this version. Try this query:

 SELECT seq FROM seq_1_to_2501 left join yourtable on seq=youridcol where youridcol is null
  • one
    If you believe the site, it should work with 10.1 - From MariaDB 10.1,
  • What's going on with the edits? - 0xdb
  • 2
    For MarioDB: SELECT s.seq FROM seq_1_to_2501 s left join tableid t on s.seq = t. id where t. id is null dbfiddle.uk/… - 2SRTVF
  • @ 2SRTVF thanks for the vigilance :) - Novitskiy Denis
  • @ 2SRTVF, thank you very much. Is this type of jpql syntax obtained? If you understand what I mean ... I just learn java, hibernate. - Bakhuss

In the code below, use sql.ru to create a table and fill it with a sequence

And in mariaDB, the sequence is built in one command, for example

SELECT * FROM seq_1_to_15_step_2;

You can also see here: Methods of generating a numeric sequence (data) in MySQL )

 create table `tableid` (`id` int); 
 insert into `tableid` (`id`) values (1),(2),(3),(4),(5), (7),(8),(9),(10),(11),(12), (14); 
 select R.`number` from ( select 1000*t4.n + 100*t3.n + 10*t1.n + t2.n + 1 as `number` from ( (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) t1 cross join (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) t2 cross join (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 cross join (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4 ) ) R left outer join `tableid` t on t.`id` = R.`number` where R.`number` <2501 and t.`id` is null order by `number` 
 |  number |
 |  -----: |
 |  6 |
 |  13 |
 |  15 |
 |  16 |
 |  17 |
 |  18 |
 |  19 |
 |  20 |
 |  ... | |
 |  2500 |

db <> fiddle here

  • Thank. It is necessary to digest. Apparently, it's still easier for me to jot down a program in java code and fill in the missing id. :) - Bakhuss