mysql - 如何在 MySQL 事件中执行多个查询

Question

所以，我在 mySQL 上创建一个事件，如果从交易日期过去 5 天，交易状态将被取消，预订将返回等等。到目前为止，这是我的代码：

CREATE EVENT cancel_transaction_event
ON SCHEDULE
    EVERY 1 DAY
    STARTS CURRENT_TIMESTAMP
COMMENT 'CHANGING STATUS OF FORFEITED TRANSACTIONS'
    DO
        BEGIN
            UPDATE transaction_tbl
            SET `status` = '0'
            WHERE DATEDIFF(CURDATE(), transaction_date) = 5 AND `status` = '1';

            UPDATE customer_tbl C
            INNER JOIN (SELECT transaction_date, customer_no
                        FROM transaction_tbl
                        WHERE (DATEDIFF(CURDATE(), transaction_date)) = 5 AND `status` = '1'
            ) T ON C.customer_no = T.customer_no
            SET C.cancelled_trans = C.cancelled_trans + 1;

            UPDATE product_tbl P
            INNER JOIN (SELECT O.product_sku, O.quantity
                        FROM order_tbl O
                        INNER JOIN transaction_tbl T
                        ON O.transaction_no = T.transaction_no
                        WHERE DATEDIFF(CURDATE(), T.transaction_date) = 5 AND T.`status` = '1'
            ) O ON P.product_sku = O.product_sku
            SET P.reserved_stock = P.reserved_stock - O.quantity,
            P.available_stock = P.available_stock - O.quantity;

            UPDATE inventory_tbl I
            INNER JOIN (SELECT O.quantity AS quantity, P.product_no AS product_no
                       FROM order_tbl O
                       INNER JOIN transaction_tbl T
                       ON O.transaction_no = T.transaction_no
                       INNER JOIN product_tbl P
                       ON O.product_sku = P.product_sku
                       WHERE DATEDIFF(CURDATE(), T.transaction_date) = 5 AND T.status = '1'
            ) O ON I.product_no = O.product_no
            SET I.reserved_stock = I.reserved_stock - O.quantity,
            I.available_stock = I.available_stock - O.quantity;
         END |

         DELIMITER ;

它单独运行，但在我运行此查询时失败。我的语法/格式有什么问题。谢谢你。

错误是：

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 10 行的 '' 附近使用正确的语法

标签： mysqleventsmysql-event