1

So, I am creating an event on mySQL which if 5 days passed from transaction date, the status of transaction will be cancelled and the reservation will return and so forth. Here's my code so far:

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 ;

It runs individually but fails when I run this query. What is wrong with my syntax/format. Thank you.

The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 10

Cheryl Blossom
  • 185
  • 1
  • 13

0 Answers0