Query to delete all records from table and its relateds on another 2 tables-CodePudding

I'm ussing Mysql 10.1.48-MariaDB-0ubuntu0.18.04.1 and I have a situation.

I have 3 tables:

|-Persons-  |
| Id        |
| Name      |
| Email     |
| Date_Last |
------------
| -Routes- |
|Id        |
|Persons_id|
|Date      |
------------
|-Payments-|
|Id        |
|Persons_id|
|Total     |
------------

We are cleaning the DB from non-active Persons. I need to delete all records from Persons where Date_Last > 1 year from now and all records saved on Routes and Paymets from this person.

I.e. A person had about 10 Routes and more than 8,000 Payments.

I can't figure out how to do it in a Query.

Any help?

Thanks in advance

CodePudding user response：

I think what you need to do is first find out what users have been inactive for one 1year or more. something like

select * from Persons where Date_last > DATEADD(year, -1, GetDate())

or find another way to load up all users

After that you can go a step further

Delete from routes where person_ID in (select ID from Persons where Date_last > DATEADD(year, -1, GetDate()) )

and the same for payments

Delete from Payments where person_ID in (select ID from Persons where Date_last  DATEADD(year, -1, GetDate()) )

be careful when you handle commands like this small mistakes can cause you a lot of data.

try those commands if you want but I first suggest changing all Delete to select * So you can know the data that you are going to delete is correct. Hope this can guide you to what you need to do

NOTE: Please dont run the above sql if you dont test it 1st. if it works it might delete data you want

CodePudding user response：

We are cleaning the DB from non-active Persons. I need to delete all records from Persons where Date_Last > 1 year from now and all records saved on Routes and Paymets from this person.

DELETE Persons, Routes, Payments
FROM Persons
JOIN Routes ON Persons.Id = Routes.Persons_Id
JOIN Payments ON Persons.Id = Payments.Persons_Id
WHERE Persons.Date_Last < CURRENT_DATE - INTERVAL 1 YEAR;

If it is possible that there exists a row in Persons which have no related row in Routes and/or Payments then use LEFT JOIN for according slave table.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a791fa27888b3d37ce8c320d4cf9c492 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=eea61c4684cc4cbc9cb6f1fe80ce1b6a https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8539eb719c85d314cba2a3880b8d6b5b https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=63c7befdb951b537c9e63199367076b8