How to use multiple sql statements in a mysql event?

Question

These are the statements

INSERT INTO toolate (name,type,date) 
SELECT  name, type ,date 
FROM homework 
WHERE date < CURRENT_DATE()

and

DELETE FROM homework WHERE date < CURRENT_DATE()

I need to combine these two so that my event will work in a proper order. Firstly the INSERT statement then the DELETE one.

That way I can still see homework that's past date while having a clean homework table and it needs to happen automatically thus why I'm using events. Of course I will welcome a different solution.

@JohnHC points out that you can leave your old records in their original table, and retrieve current records with a view mentioning date < CURRENT_DATE(). He's right. The only reason to move rows from one table to another is when you have many millions of stale rows of data to put into archives. — O. Jones
– O. Jones, Commented Feb 24, 2017 at 12:38

Darshan Mehta · Accepted Answer · 2017-02-24 12:36:21Z

1

You can't combine these two in a single query. However, an alternative would be to use STORED PROCEDURE and execute these two inside a transaction with EXIT HANDLER e.g.:

BEGIN

    START TRANSACTION;

        DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        BEGIN
            ROLLBACK;
            EXIT PROCEDURE;
        END;

        INSERT INTO toolate (name,type,date) 
        SELECT  name, type ,date 
        FROM homework 
        WHERE date < CURRENT_DATE()

        DELETE FROM homework WHERE date < CURRENT_DATE()

    COMMIT;
END

This will make sure both of these queries are executed sequencially, and if DELETE query fails, INSERT will be rolled back.

Here's MtSQL's documentation for stored procedures.

answered Feb 24, 2017 at 12:36

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use multiple sql statements in a mysql event?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related