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

krishna ramisetty 21 Reputation points
2022-01-26T11:59:26.267+00:00

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  

168731-table1.png
Please help me

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2022-01-27T13:55:22.073+00:00

    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
    

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-01-26T13:31:33.163+00:00
    0 comments No comments

  2. LiHong-MSFT 10,046 Reputation points
    2022-01-27T03:20:37.51+00:00

    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.