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

Question

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)  
AS   
BEGIN  
DELETE [dbo].[ExamSet] Where CreatedDate<DATEADD(dd, -@day, GETDATE());  
END  

Please help me

Thanks

Answer 1

Based on your new requirements

 CREATE PROCEDURE sp_deleteLog(@day Int)
 AS 
 BEGIN
 DELETE FROM [dbo].[ExamLog]
     WHERE Setid IN (SELECT SetId FROM  [dbo].[ExamSet] Where CreatedDate<DATEADD(dd, -@day, GETDATE()));
 END

Answer 2

What you describe is called "cascade delete".

See:
https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

Answer 3

Hi @krishna ramisetty
What you need is 'On Delete Cascade'
Please check this code and have a test:

use tempdb  
go  
  
create table ExamSet  
(  
Setid int primary key ,  
CreatedDate datetime,    
SetName varchar(10)  
)  
create table Examlog  
(  
RowID bigint  primary key,  
Setid int  ,  
Created_on datetime  
)  
insert into ExamSet values (30,'2019-03-08','A'),(31,'2021-04-02','B'),(32,'2017-08-23','A')  
insert into Examlog values (199383,30,'2019-03-08'),(199384,30,'2019-03-08'),(199385,30,'2019-03-08'),(199386,30,'2019-03-08')  
,(199387,32,'2017-08-23'),(199388,32,'2017-08-23'),(199389,32,'2017-08-23')  
  
SELECT * from ExamSet;  
SELECT * from Examlog;  
  
alter table Examlog  
add constraint FK_Examlog_Setid_delete  
foreign key(Setid) references ExamSet(Setid)  
on delete cascade  
  
DELETE ExamSet Where CreatedDate<DATEADD(year,-4, GETDATE());  
  
SELECT * from ExamSet;  
SELECT * from Examlog;  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.