Can we write multiple queries in a MySQL event?

Question

I want to execute the following queries with help of Mysql event But when I add the delete statement in the event and try to create it, gives me Mysql Error. If I chose to skip the delete statement the event gets created without any problem.

INSERT INTO tbl_bookings_released
(
    id, row, seatnum, price,theatre_id, play_id, show_id, showtime, show_date, 
    isbooked, inserted_at, inserted_from, booking_num, tot_price, subzone_id, 
    zone_id, txn_id
)
SELECT 
    id, row, seatnum, price,theatre_id, play_id, show_id, showtime, 
    show_date, isbooked, inserted_at, inserted_from, booking_num, 
    tot_price, subzone_id, zone_id, txn_id
FROM tbl_bookings
WHERE (
    UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( inserted_at )
) /60 > 2
AND booking_num NOT
IN (
    SELECT booking_id
    FROM tbl_cust_booking
);

DELETE
FROM tbl_bookings
WHERE (
    UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( inserted_at )
) /60 > 2
AND booking_num NOT
IN (
    SELECT booking_id
    FROM tbl_cust_booking
);

score 19 · Accepted Answer · answered Nov 26 '15 at 06:34

Here is an example modified from the documentation that execute multiple queries for an event:

delimiter |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
    INSERT INTO tbl_bookings_released
    (
        id, row, seatnum, price,theatre_id, play_id, show_id, showtime, show_date, 
        isbooked, inserted_at, inserted_from, booking_num, tot_price, subzone_id, 
        zone_id, txn_id
    )
    SELECT 
        id, row, seatnum, price,theatre_id, play_id, show_id, showtime, 
        show_date, isbooked, inserted_at, inserted_from, booking_num, 
        tot_price, subzone_id, zone_id, txn_id
    FROM tbl_bookings
    WHERE (
        UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( inserted_at )
    ) /60 > 2
    AND booking_num NOT
    IN (
        SELECT booking_id
        FROM tbl_cust_booking
    );

    DELETE
    FROM tbl_bookings
    WHERE (
        UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( inserted_at )
    ) /60 > 2
    AND booking_num NOT
    IN (
        SELECT booking_id
        FROM tbl_cust_booking
    );


      END |

delimiter ;

Thanks, it worked. I was using phpmyadmin to create event and in the definition text area I had just written my desired queries without BEGIN and END tags, I guess that was the problem. I edited my existing event to add BEGIN and END and it worked! — Aditya Achar, Nov 26 '15 at 06:44

score 18 · Answer 2 · answered Jul 05 '18 at 10:07

18

if you are using phpmyadmin to create event then add multiple queries inside BEGIN and END tags

answered Jul 05 '18 at 10:07

Rakesh Soni

10,135
5
44
51

1

Note to self: Seems to only work with a blank line after BEGIN and before END – alpha1 Aug 10 '18 at 16:35
In MariaDB (PHPMyAdmin), BEGIN END is not working even I am giving black line after BEGIN and before END – Abhinav Kumar Singh May 14 '20 at 07:57
This is my code : DELIMITER @@; CREATE EVENT test_event_03 ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 72 HOUR DO BEGIN UPDATE complete_exam_set SET live_status = '1' WHERE test_time < CURRENT_TIMESTAMP ; UPDATE complete_exam_set SET live_status = '2' WHERE end_time < CURRENT_TIMESTAMP ; END; @@; DELIMITER ; – Abhinav Kumar Singh May 14 '20 at 08:26

score 1 · Answer 3 · edited May 14 '20 at 17:44

DELIMITER @@;

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 72 HOUR
DO
BEGIN

    UPDATE complete_exam_set 
   SET live_status = '1' 
   WHERE test_time < CURRENT_TIMESTAMP ;

   UPDATE complete_exam_set 
   SET live_status = '2' 
   WHERE end_time < CURRENT_TIMESTAMP ;

END;
@@;

DELIMITER ;

Why that red cross is coming near END what's wrong?

score 0 · Answer 4 · answered Jan 26 '23 at 19:13

0

You got the red x because you used the semicolon in the last line..

answered Jan 26 '23 at 19:13

andyf

3
2

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/33715915) – SelVazi Jan 31 '23 at 14:57

Can we write multiple queries in a MySQL event?

4 Answers4