What is the right syntax of IF statement in MySQL?

Question

I have a small and simple MySQL code. But whenever I run it, I get error #1064. Can you tell me what is my mistake here?

IF ((SELECT COUNT(id) FROM tbl_states) > 0) THEN
    BEGIN
        SELECT * FROM tbl_cities;
    END
END IF

I also used some other conditions like the below one, but again I got an error.

IF (1=1) THEN
    BEGIN
        SELECT * FROM tbl_cities;
    END
END IF

What I actually want to do is something like this:

IF ((SELECT COUNT(id) FROM tbl_states) > 0) THEN
    BEGIN
        UPDATE ...
    END
ELSE
    BEGIN
        INSERT ...
    END
END IF

Solution 1

If it is a procedure you're writing you should try:

BEGIN
    IF ((SELECT COUNT(id) FROM tbl_states) > 0) THEN
        SELECT * FROM tbl_cities;
    END IF
END

If it is a query, BEGIN and END have nothing to do here.

Edit

Well, there is not really more to say, IF ((SELECT COUNT(id) FROM tbl_states) > 0) THEN SELECT * FROM tbl_cities; END IF is simply not respecting the basic MySQL SELECT statement.

You should start with SELECT... etc...

Solution 2

I think you just need this:

SELECT * FROM tbl_cities WHERE EXISTS (SELECT * FROM tbl_states)