I'm trying to learn the ropes of some new MySQL syntax and am having trouble. This should be simple...
I'm following along with the manual here: http://dev.mysql.com/doc/refman/5.5/en/case.html
but I keep getting a syntax error. Here is my routine:
# Drop anonymous accounts, if any
USE mysql;
CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost')
WHEN 1 THEN
DROP USER ''@'localhost';
FLUSH PRIVILEGES;
END CASE;
The error is:
ERROR 1064 (42000): 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 'CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost')
Thanks in advance.
After reviewing your comment regarding the fixed statement but immediate second issue, it was clear that you're not using this within a stored procedure or function. The documentation for
flow control statements
very subtly states that they need to be within stored procedures/functions.Update your code to be within a procedure, and then just call the procedure to execute:
Also note that I added a catch-all
ELSE
block; if you don't catch the value,CASE
will throw a "Case not found" warning - which may or may not be desirable.