mysqlmysql-event

Can we write multiple queries in a MySQL event?


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
);

Solution

  • 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 ;