0

I created an EVENT basically does two things:

  1. SELECT the rows that are more than 30 days old and INSERT into the achieve table.
  2. DELETE the rows that are older than 30 days old in the trends table.

I separately tested the two scripts and they both work fine but I got a syntax error when put them in the EVENT.

If I only user INSERT INTO or DELETE script in the event body, it saves fine.

The scripts are:

CREATE DEFINER=`root`@`localhost` 
    EVENT `trend_decayer` ON SCHEDULE EVERY 1 DAY 
    STARTS '2016-08-30 00:00:00' ON COMPLETION NOT PRESERVE ENABLE 
    COMMENT 'Decay trends that are older than 30 days' 
DO
INSERT INTO `achieve` 
    SELECT *, FROM_UNIXTIME(`trends`.`timestamp`) AS `datetime` 
    FROM `trends`
    WHERE `t`.`timestamp` < UNIX_TIMESTAMP(CAST(DATE_SUB(NOW(), INTERVAL 30 DAY) AS DATETIME)) 
    ORDER BY `datetime` DESC;
DELETE 
    FROM `trends` 
    WHERE `timestamp` < UNIX_TIMESTAMP(CAST(DATE_SUB(NOW() , INTERVAL 30 DAY) AS DATETIME));

The error message is :

MySQL said: #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 
'INTO `achieve` 
    SELECT *, FROM_UNIXTIME(`timestamp`) AS `datetime` FR' at line 2`
noob
  • 480
  • 3
  • 20

0 Answers0