Creating a Stored Procedure to Delete Records from Tables

Off-Topic
1

What am I doing wrong? Trying to execute this within MemSQL Studio:

USING db_data CREATE PROCEDURE sp_delete_data() AS
BEGIN
    DELETE FROM tbl_prices_01
    WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_01);
    DELETE FROM tbl_prices_02
    WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_02);
    DELETE FROM tbl_prices_03
    WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_03);
END;
2

What error are you getting? If you are deleting all the old data, is there a reason you aren’t truncating prior to insert?

3

I’m trying to create a stored procedure within MemSQL, yet have no idea how to do it. Basically the only commands would be those 3 delete statements.

4

Run this as a script, not a single statement. You need to start and commit the transaction, as well as change the delimiter so your client knows when to start and stop the procedure. If any of the deletes fail, all 3 of the tables will return to their previous state.

DELIMITER //
CREATE PROCEDURE db_data.sp_delete_data() AS
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;  -- rollback any error in the transaction
    END;

    START TRANSACTION;
        DELETE FROM tbl_prices_01
        WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_01);
        DELETE FROM tbl_prices_02
        WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_02);
        DELETE FROM tbl_prices_03
        WHERE uploadTime < (SELECT MAX(dataUploadTime) FROM tbl_prices_03);
    COMMIT;
END;
//
DELIMITER ;