Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Select then Delete

I need to clean out some old data from my database tables. Below shows two tables, app_status and app_personal_details.

enter image description here

I have the following query to select all the records (applications) from the app_status table which have been created over 12 months from todays date

select status_id  
from dbo.app_status  
where submission_date <= dateadd(month, -12, getdate()) order by 1

I then want to delete all the records from the app_personal_details table where the application_id exists in the list of retrieved status_id above.

I hope this makes sense. I would like to know a quick way of deleting the data from both tables.

Thanks for your help.

like image 823
tcode Avatar asked Feb 19 '23 18:02

tcode


2 Answers

delete from app_personal_details
where application_id in (
    select status_id   
    from dbo.app_status   
    where submission_date <= dateadd(month, -12, getdate())
)
like image 98
D'Arcy Rittich Avatar answered Feb 21 '23 07:02

D'Arcy Rittich


You can use a subselect.

DELETE FROM app_personal_details
WHERE application_id IN
(
    SELECT status_id  
    FROM dbo.app_status  
    WHERE submission_date <= DATEADD(month, -12, GETDATE())
)
like image 32
Mark Byers Avatar answered Feb 21 '23 08:02

Mark Byers