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

1

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

2

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

3

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.

4

Why not setup a DELETE CASCADE foreign key.
https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-foreign-key/

5

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;

6

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