SQLTeam.com | Weblogs | Forums

How to delete all records in a table based on selected record from another table

Hello Everyone,

I have below query to delete record from ' ExamSet '. but based on the ' ExamSet ' table ' Setid ' column value i need to remove all reacords in ' ExamLog ' table. here i need to select old Setids from ExamSet table CreatedDate (older records based on @day parameter)

I need to delete all records (associated to Setid coming from ExamSet table) from ExamLog table only not from ExamSet table

CREATE PROCEDURE sp_deleteLog(@day Int)
      DELETE [dbo].[ExamSet] Where CreatedDate<DATEADD(dd, -@day, GETDATE());

Please help me


maybe you should look at your previous post, the solution is the same

hi Mike,

this is another scenario. Based on the records coming from 'ExamSet' table(Setid), i need to delete all records associated to the Setids in 'ExamLog' table only not from ExamSet table in same storedProcedure.

Why not setup a DELETE CASCADE foreign key.

this causes alot of locks on large tables and not a good idea (IMO). You should know what is being deleted and delete accordingly. 2 statements
declare @DeleteDate datetime = DATEADD(dd, -@day, @DeleteDate)

DELETE el from dbo.ExamLog el join [dbo].[ExamSet] es on el.SetID = es.SetID and es.CreatedDate< @DeleteDate;

DELETE [dbo].[ExamSet] Where CreatedDate<@DeleteDate;

True but the OP has asked similar questions before and is obviously not coping with the code.