You have a table table1 that contains id column, that is int(11), not null, auto_increment and starts from 1.
Suppose, you have 10,000 records. It is clear the id of the last record is 10,000. Once you removed 3 records, you have 9,997 records in the table, but the last record id value is still 10,000 (if the last record was not deleted).
How to display what records have been removed using 1 sql query?
Thank you.
I think easiest would be to have a dummy/temp table with just ids. 1-1000 then left join to that table.
But be sure to remove the "deleted" records from your dummy/temp table once you're done. Otherwise, they will show up every time.
>> EDIT << You can do self join to figure out if you're missing ids....
select a.id + 1 MissingIds
from <table> a
left join <table> b
on a.id = b.id - 1
where b.id is null
and a.id < 10000
You may find this sql useful here http://www.sqlservercurry.com/2009/06/find-missing-identity-numbers-in-sql.html
I used this answer as a reference.
You can use the following query to find the gaps, which in essence will give you the deleted record "ranges". For example, in the below example, you get 2 rows back in the final result, and the values are 2 and 3, and 6 and 7. So you know that rows with IDs of 2 through 3 have been deleted, and rows with IDs of 6 through 7 have been deleted (for a total of 4 deleted rows).
I believe this meets your requirement of getting the final result in "1 SQL query", and plus, no intermediate or dummy tables are used.
delimiter $$
use test
$$
create table mytable (id int not null auto_increment, name varchar(100), primary key (id));
$$
insert into mytable (name) values('a')$$
insert into mytable (name) values('b')$$
insert into mytable (name) values('c')$$
insert into mytable (name) values('d')$$
insert into mytable (name) values('e')$$
insert into mytable (name) values('f')$$
insert into mytable (name) values('g')$$
insert into mytable (name) values('h')$$
delete from mytable where id = 2$$
delete from mytable where id = 3$$
delete from mytable where id = 6$$
delete from mytable where id = 7$$
SELECT (t1.id + 1) as gap_starts_at
, (SELECT MIN(t3.id) -1
FROM mytable t3
WHERE t3.id > t1.id) as gap_ends_at
FROM mytable t1
WHERE NOT EXISTS (SELECT t2.id FROM mytable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
Output:
gap_starts_at gap_ends_at
2 3
6 7
DECLARE @myTestTable1 TABLE
(
id INT IDENTITY(1,1) NOT NULL
,testVal int
)
DECLARE @increment AS int = 1
WHILE (@increment <= 10000)
BEGIN
INSERT INTO @myTestTable1
VALUES (@increment)
SET @increment += 1
END
DELETE FROM @myTestTable1 WHERE id IN (100,200,300)
--SELECT * FROM @myTestTable1
;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from @myTestTable1)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @myTestTable1 tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);
But it's taking lot of time. We have to reduce the time.
So to start with, I am going to show the easiest way to generate 10.000 records. No huge-ass queries, no variables. Execution time: ~3ms. LINK
Now about that trigger that I promised. LINK
As you can see, it is really easy to create one. Keep in mind that the trigger is better not only for no need of various joins, but you can also store the date, user id, etc. etc. (very expandable example that is). And the main point of trigger over joins is: you do not care how many records there were/are/will be. You don't need to be strict about the size. That's why I called the answer of sam yi not professional enough. Sorry for misunderstanding, I am pretty sure none of us wanted to insult anyone.
By creating this example I did learn a few things. Hopefully you did too :)
