MySQL Event If Statement END IF Error

Question

Trying to create a MySQL event that resets a soldout status (indicated as 1).

The logic I'm trying to create is a simple IF statement.

If soldout = 1 AND Quantity > 0 in the same row

UPDATE soldout to 0

Here is my event

CREATE EVENT soldoutreset
ON SCHEDULE EVERY 5 MINUTE
DO
IF db.Inventory.soldout = 1
AND db.Inventory.Quantity > 0 THEN
UPDATE db.Inventory SET soldout = 0
WHERE soldout = 1 AND Quantity > 1
END IF;

This is resulting in an error, 'END IF' at line 9

#1064 - 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 'END IF' at line 9

Can anyone tell why this is not working?

Gab · Accepted Answer · 2017-03-13 17:13:24Z

6

This should work, you are missing a part of the syntax:

DELIMITER |

CREATE EVENT soldoutreset
ON SCHEDULE EVERY 5 MINUTE
    DO
    BEGIN
        IF db.Inventory.soldout = 1 AND db.Inventory.Quantity > 0 THEN
            UPDATE db.Inventory SET soldout = 0
            WHERE soldout = 1 AND Quantity > 1;
        END IF;
    END |

DELIMITER ;

Update:

If the event is not running as expected, it might be because the event scheduler is off. It can be enabled with:

SET GLOBAL event_scheduler = ON;

edited Mar 13, 2017 at 17:13

answered Mar 13, 2017 at 16:20

Gab

3,5381 gold badge13 silver badges25 bronze badges

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

9 Comments

Brian Bruman Over a year ago

I am still getting the same (or similar error) ...to use near 'END IF; END' at line 8

Gab Over a year ago

In my initial answer I missed BEGIN dans I updated the answer since. Try again with the current version please

Brian Bruman Over a year ago

I believe I had the BEGIN inititally, I don't see an edit. Still, I try and still getting the END IF; END error.

Gab Over a year ago

@BrianBruman I was missing the ; at the end of the update query, please try again.

Brian Bruman Over a year ago

Yes it processed correctly. Thank you for the help with the missing syntax. Still looking into why it is not working properly, I'm trying different things. After 5 minutes my test row with a Quantity of 17 and a soldout of 1 still shows 1 after 5 minutes which is not my intended 0

|

Collectives™ on Stack Overflow

MySQL Event If Statement END IF Error

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related