CODEWITHSUNDEEP
sqlsql-server
gopim
gopim

Reputation: 35

How to Finding Random records which are not available in the table after deleted ?is it possible to find out?

I have table called Student and table columns are Id(identity Key) & student Name. It having 100 records from 1 to 100.I deleted randomly 5 records from the student table. Now the table having only 95 records. How do I get logic what are the 5 id's not available in the table. Can anyone help to figure out the correct query? I tried to write query by fetching the records every records id is increasing "1" in the loop if any record not matched that records would go to temp table and then loop will continues until last record 100th. The interviewer said it was not correct way and didn't tell anyway to find out. I am confused whether it is possible or not.

Upvotes: 0

Views: 120

Answers (5)

Thibault Jacquesson
Thibault Jacquesson

Reputation: 1

Try something like this:

  1. Duplicate your Student table before deleting
  2. Process to random delete
  3. Then try: 
    SELECT    * 
FROM      Student 
EXCEPT 
SELECT    * 
FROM      StudentDuplicated

Upvotes: 0

Radek Gąska
Radek Gąska

Reputation: 56

If I understand your query correctly, you can:

  1. List all IDs from the given range with CTE
  2. Select all IDs from CTE which do not exists in your table
WITH N(V) AS (
  SELECT 1 
  UNION ALL
  SELECT V + 1 FROM N
  WHERE V < 100
)
SELECT *
    FROM N
    where V not in (select id from your_table)

I am note sure if it is super elegant solution and covers all possible scenarios, but should do the work in most typical cases.

Upvotes: 0

PankajSanwal
PankajSanwal

Reputation: 1029

I believe question is to find the identity values which were deleted in the past form a table and not about how to output the ids to another table at runtime while they are being deleted.

If you want to find out the missing identities from your table use:

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select IDENT_CURRENT('Your_Table'))
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN Your_Table tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);

Idea is to use recursive CTE to generate a table with all identity values and then left join with the table from which ids were deleted.

If you want to output the rows which are being deleted by a delete statement at run time then @Gordon Linoff solution above is correct.

Upvotes: 0

nimajv
nimajv

Reputation: 433

it can work but not for first ID

select s1.id+1  from student s1
left join student s2 on s1.id = s2.id +1
where s2.name is null

and it can work for first ID , Not for last ID


select s1.id-1  from student s1
left join student s2 on s1.id-1 = s2.id 
where s2.name is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270533

You would use the output clause:

declare @ids table (id int);

delete s
    output deleted.id into @ids
    from student s
    where . . .;

select *
from @ids;

Upvotes: 1

Related Questions