I need to clean out some old data from my database tables. Below shows two tables, app_status and app_personal_details.
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.
delete from app_personal_details
where application_id in (
select status_id
from dbo.app_status
where submission_date <= dateadd(month, -12, getdate())
)
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())
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With