Good day.
I need to create an event MySQL
that runs on the first day of every month and inserts records into a table based on the number of records fetched from another table.
I have tried with this structure:
CREATE EVENT statistics
ON SCHEDULE EVERY 1 MONTH
DO
SELECT * FROM `tabla_lectura`
FOR EACH ROW
SET @title = (SELECT `title` FROM `tabla_lectura`)
SET @hits = (SELECT `hits` FROM `tabla_lectura`)
BEGIN
INSERT INTO `tabla_escritura` (title, hits) VALUES (@title,@hits)
END
END FOR
But I get this error:
1064 - Something is wrong in your syntax near 'EACH ROW SET @title = (SELECT
title
FROMtabla_lectura
) ' on line 5
The idea is that for each record in the tabla_lectura
, insert those two values intotabla_escritura
What is the correct structure to achieve it?
Honestly, this is my first time trying to create events and I'm not very familiar with the syntax.
Some comments:
FOR EACH ROW
actually applies to triggers, see 24.3.1 Trigger Syntax and Examples .One possible option to achieve what you need is the following:
I'm not sure if you should query the entire table
tabla_lectura
every month when the EVENT is executed, but you can make any necessary modifications.