i'm trying to update existing value every one hour if the condition is valid
is it possible to write if statement inside event ?
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'add one every hour if its lees than 10 '
DO
if score > 10
UPDATE my_table SET score=score+1 WHERE ID=1
Of course it's possible. Just three problems with your code:
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR /*Problem 1: This is every 1 hour starting from the time you created the event. Probably not what you want.*/
COMMENT 'add one every hour if its lees than 10 '
DO /*Problem 2: You'll have multiple statements here. Tell this MySQL or it will think that your event declaration will be finished after the first statement.*/
if score > 10 /*Problem 3: The computer will ask "which score?"*/
UPDATE my_table SET score=score+1 WHERE ID=1
Write your code like this:
DELIMITER $$
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
STARTS '2014-03-12 09:00:00'
COMMENT 'add one every hour if its lees than 10 '
DO
BEGIN
SET @my_score = (SELECT score FROM my_table WHERE ID = 1);
IF (@my_score > 10) THEN
UPDATE my_table SET score=score+1 WHERE ID=1;
END IF;
END $$
DELIMITER ;
Yes, you can have IF or any other conditions and multi statement code blocks in an event definition.
When you have multiple statements to be processed, you need to enclose them between begin - end.
Example:
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'add one every hour if its lees than 10 '
DO
BEGIN
if ( score > 10 ) THEN
UPDATE my_table SET score=score+1 WHERE ID=1;
elseif ( score < 10 ) THEN
-- add your statement here
else
-- add your statement here
end if;
END;
Refer to: