MySql Event to Delete Data from 2 Tables Older Than 2 Months

Question

Im new to MySql events and I never used them before so please to bear with me. With My DB I have order_details and invoices tables which I need to create an Event to delete all the records older than 2 months and where order_details status = 5 and invoices status = 3 and I want the event to make the deleting every day at 3AM How I may accomplish this Any help will be much appreciated

Update :

Both tables have timestamp column named created_at

Tables Definition

A - order_dateils :

id
order_bar
description
status
created_at

B - invoices :

id
invoice_id
status
created_at

Which of the 2 questions here (delete and create event) are you having problems with. AND where order_details status = 5 and invoices status = 3 is not clear nor is how you link orders to invoices (if that is the intention). — P.Salmon, Sep 05 '20 at 13:55
@P.Salmon Actually i saw to many examples for using events to delete from 1 table a time, in my case i have two tables both have timestamp column created_at and the order_details status must be =5 and the invoices status must be = 3 so my question is that possible — Mohammed Riyadh, Sep 05 '20 at 14:03
Does this answer your question? [How to delete from multiple tables in MySQL?](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) — P.Salmon, Sep 05 '20 at 14:05
@P.Salmon No my friend, i know how to delete from tables what Im looking for is an Event to do the deleting every day at 3AM — Mohammed Riyadh, Sep 05 '20 at 14:10
Creation and scheduling events is covered here - https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html and is pretty straightforward did you try something and is there any specific problem you are having? — P.Salmon, Sep 05 '20 at 14:27

Erick · Answer 1 · 2020-09-08T13:03:56.453

0

    CREATE EVENT delete_every_day
        ON SCHEDULE
          EVERY 1 DAY STARTS DATE_ADD(CURDATE(), INTERVAL'1 3' DAY_HOUR)
        -- Will delete every day record from invoices with status = 3 and more than 2 
        -- month old, and the order_details with status = 5 and more than 2 month old
        -- It will start deleting next day at 3:00 AM.
        DO
          BEGIN
            DELETE FROM your_schema.invoices
                 WHERE TIMESTAMPDIFF(MONTH,created_at, NOW()) > 2
                 AND status = 3;
            DELETE FROM your_schema.order_details
                 WHERE TIMESTAMPDIFF(MONTH,created_at, NOW()) > 2
                 AND status = 5;
          END

I hope this may answer your question. Make sure to include your schema (database name) with your table name such as [schema_name.table_name] to make sure the event is associated with the right tables. Good luck.

edited Sep 08 '20 at 13:03

answered Sep 05 '20 at 15:42

Erick

301
3
12

Thanks for your answer, but before trying it isn't the TIMESTAMPDIFF missing the months (the unit) ? – Mohammed Riyadh Sep 07 '20 at 13:35
@Ali Adil, You're100% right. My wrong. There must be like "TIMESTAMPDIFF(MONTH, created_at, NOW()). Sorry for that. I just edit it! – Erick Sep 07 '20 at 14:04
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1 3' DAY_HOUR) DO BEGIN DELETE FROM forval_DB.invoice WHERE TIMESTAMPDIFF(MONTH' at line 1 – Mohammed Riyadh Sep 07 '20 at 17:54
I cannot identify any SQL Syntax mistake in the code. I'll appreciate you to point it out if you do. – Erick Sep 07 '20 at 20:38
The error was in the DATE_ADD function, missing the INTERVAL clause. It's fixed now! – Erick Sep 08 '20 at 13:06

MySql Event to Delete Data from 2 Tables Older Than 2 Months

1 Answers1