Share via

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.
13,362 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. Tom Phillips 17,721 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
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-01-26T13:31:33.163+00:00
    0 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.