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
table1

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

Please help me

Thanks

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.
https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-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.