What is wrong with my MySQL CASE/WHEN syntax?

Question

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.

newfurniturey · Accepted Answer · 2012-08-27T05:55:33.180

2

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:

USE mysql;

DROP PROCEDURE p;
DELIMITER |
CREATE PROCEDURE p() BEGIN
    CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost') 
        WHEN 1 THEN
            DROP USER ''@'localhost';
            FLUSH PRIVILEGES;
        ELSE
            SELECT 'no users found!';
    END CASE;
END;
|

CALL p();

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.

edited Aug 27 '12 at 05:55

answered Aug 26 '12 at 19:48

newfurniturey

37,556
9
94
102

OK, you got the CASE/WHEN syntax working! Now the DROP USER ''@'localhost'; is throwing a syntax error? This is should be simple...? – Adam Friedman Aug 26 '12 at 20:03
@AdamFriedman Ah - I see the real issue now, it's a completely different problem, which actually means your original `CASE`-statement was correct. You're not using the `CASE` inside a procedure or function - which is a requirement. I'll update my answer to reflect. – newfurniturey Aug 26 '12 at 20:20
Your solution works. Down the rabbit hole I go. Now I have to learn about stored procedures. Thanks so much for getting me here -- I think. ;) – Adam Friedman Aug 27 '12 at 20:12

What is wrong with my MySQL CASE/WHEN syntax?

1 Answers1

Linked