MySQL: "IF (EXISTS A) AND (NOT EXISTS B) THEN..." is not working-CodePudding

I would like to user condition like this:

IF (EXISTS A) AND (NOT EXISTS B) THEN...

A and B conditions are like this:

SELECT * FROM table WHERE flag=1

E.g.:

IF EXISTS (SELECT * FROM table WHERE flag=1) 
    AND NOT EXISTS (SELECT * FROM table WHERE flag=2)
THEN
    INSERT INTO table (id, name, flag) VALUES (0, 'name', 2);
END IF;

In this case, the "(EXISTS A) and (NOT EXISTS B)" condition is true. but the query don't enter into 'THEN'. Could i know what's wrong?

CodePudding user response：

The IF you are using is not available outside of a stored procedure or maybe trigger. Instead, you should use an INSERT INTO ... SELECT:

INSERT INTO yourTable (id, name, flag)
SELECT 0, 'name', 2
WHERE EXISTS (SELECT 1 FROM yourTable WHERE flag = 1) AND
      NOT EXISTS (SELECT 1 FROM yourTable WHERE flag = 2);