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 payment_status = 3 and invoices status = 2 and I want the event to make the deleting every day at 3AM How I may accomplish this Any help will be much appreciated

Both tables have timestamp column named created_at

Tables Definition

A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

I tried below command but Im getting errors,

CREATE EVENT IF NOT EXISTS daily_delete ON SCHEDULE EVERY 1 DAY STARTS 2020-09-13 04:00:00 DO BEGIN DELETE FROM bariq_DB.invoice where timestampdiff(MONTH,created_at,NOW()) > 2 and status = 2; delete from bariq_DB.order_details where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; END

Errors :

    ERROR 1064 (42000): 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 '' at line 1
ERROR 1064 (42000): 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 'END' at line 1

nbk · Accepted Answer · 2020-09-13T14:33:09.690

When you have multiple statements you need not only a BEGIN and END

But also a DELIMITER

One error is also dates have to be in quotes as you can see in the code

DELIMITER //
CREATE EVENT 
IF NOT EXISTS daily_delete 
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00' 
DO 
BEGIN 
    DELETE 
    FROM bariq_DB.invoice 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2; 
    delete from bariq_DB.order_details 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; 
END;//
DELIMITER ;

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

1 Answers1