Menu
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.
903
Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet. 3. This will open up Scheme Changes History report which will have the details about who deleted the SQL Server Database along with the timestamp when the database was deleted.
Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database. SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> ROLLBACK; Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.
The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.
Retrieval with SQL. In SQL, to retrieve data stored in our tables, we use the SELECT statement. The result of this statement is always in the form of a table that we can view with our database client software or use with programming languages to build dynamic web pages or desktop applications.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
