I what to create an event that delete rows fom a table that are older than 15 minus and add their value back to other table. In the code below mysql said that I have a syntax error on line 5
CREATE EVENT add_reserve_to_store_products_and_delete_reservations
ON SCHEDULE EVERY 15 MINUTE
DO
BEGIN
START TRANSACTION;
UPDATE zaloga z JOIN (
SELECT ID_skladisca, ID_izdelek, SUM(kolicina) AS reserve_sum
FROM reserve WHERE time <= NOW() - INTERVAL 15 MINUTE
GROUP BY ID_skladisca, ID_izdelek
) r ON z.ID_izdelek = r.ID_izdelek AND z.ID_skladisca = r.ID_skladisca
SET z.kolicina = z.kolicina + r.reserve_sum;
DELETE FROM reserve WHERE time <= NOW() - INTERVAL 15 MINUTE;
COMMIT;
END;
I tried removing start transaction and delete but then i get error on commit but it is super importent that it is one TRANSACTION
>Solution :
If a CREATE EVENT contains a compound statement (i.e. anything with a BEGIN…END block), then the semicolons between statements are ambiguous with respect to the semicolon at the very end of the whole CREATE EVENT. You need to change the DELIMITER in the MySQL client to define such stored routines.
This is the same for other stored routines such as procedures, functions, and triggers. See https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html for more explanation and examples.