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

krishna ramisetty 21 Reputation points

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


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,036 questions
No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,611 Reputation points

    Based on your new requirements

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

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,611 Reputation points
  2. LiHong-MSFT 9,986 Reputation points

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

    use tempdb  
    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')  
    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,

    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.