phpmyadmin|How to create an event do 2 actions

Question

I want to create an event that can do two actions but I don't know how. Here is the query:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO SET @p=1
UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC

I tried also to write AND between the actions and it still writes an error.

The error is:

\#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 'UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC' at line 2

Answer 1

You are missing the semicolons ; after your statements.

Try this:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' 
      DO SET @p=1;
UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC;

Answer 2

You can wrap the body of your event in a BEGIN ... END compound statement block:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO BEGIN
  SET @p=1;
  UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC;
END

Note that each statement must be terminated with a semicolon, so you must configure your client to use a different statement delimiter in order that it does not think the first encountered semicolon is the end of the CREATE EVENT command (how to do this will depend on your client, but in the MySQL command line tool, you can use the DELIMITER command and in phpMyAdmin you can set the delimiter below the SQL input box).

Alternatively, in this case you can use the multiple-table UPDATE syntax to perform variable initialisation such that you only require a single simple statement:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO
  UPDATE   users, (SELECT @p:=0) init
  SET      users.rate = (@p:=@p+1)
  ORDER BY users.power DESC