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

Answer 1

    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.