I'm trying to create this event but I can't find the syntax error.
CREATE
EVENT respaldoRegRawInformeAperturaLocal
ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00'
DO BEGIN
-- INSERT INTO BACKUP TABLE
INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId)
-- GET DATA
SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId
FROM regRawInformeAperturaLocal
WHERE regRawInformeAperturaLocal.date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
-- DELETE DATA FROM ORIGINAL TABLE
DELETE FROM regRawInformeAperturaLocal WHERE regRawInformeAperturaLocal.date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
END;
[CODE EDIT 1]
CREATE
EVENT respaldoRegRawInformeAperturaLocal
ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00'
DO BEGIN
-- INSERT INTO BACKUP TABLE
INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId)
-- GET DATA
SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId
FROM regRawInformeAperturaLocal
WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
-- DELETE DATA FROM ORIGINAL TABLE
DELETE FROM regRawInformeAperturaLocal WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
END;
[EDIT] This is the syntax error message:
[Err] 1064 - 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 11
I could make it work:
I have to add the DELIMITER and change the syntaxis from END; to END|
Anyway here's the code:
DELIMITER |
CREATE
EVENT IF NOT EXISTS respaldoRegRawInformeAperturaLocal
ON SCHEDULE EVERY 1 DAY STARTS '2017-08-03 22:00:00'
DO BEGIN
-- INSERT INTO BACKUP TABLE
INSERT INTO regRawInformeAperturaLocalBACKUP (regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId)
-- GET DATA
SELECT regRawInformeAperturaLocalId, rawInformeAperturaLocal, done, date, deviceId
FROM regRawInformeAperturaLocal
WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
-- DELETE DATA FROM ORIGINAL TABLE
DELETE FROM regRawInformeAperturaLocal WHERE date < DATE_SUB(NOW(), INTERVAL 5 MONTH);
END|
DELIMITER ;
Now I have to test it but I could create it so I guess It's fine.
[EDIT] The event works like a charm.