IF NOT EXIST NOT WORKING

Question

IF NOT EXISTS(SELECT * FROM `user` WHERE `name`='Rutvij' AND `lang`='python')
    BEGIN
        INSERT INTO `user` VALUES ('Rutvij', 'python', 25)
    END
ELSE
    BEGIN
        UPDATE user SET `name`='Kanzaria' WHERE `name`='Rutvij'
    END

I am trying the above query in phpmyadmin sql area. I am using xampp. It is throwing error stating that

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 'IF EXISTS(SELECT * FROM user) SELECT name FROM use' at line 1

I have also tried the below code

IF NOT EXISTS(SELECT * FROM user WHERE name='Rutvij' AND lang='python')
    BEGIN
        INSERT INTO user VALUES ('Rutvij', 'python', 25)
    END
ELSE
    BEGIN
        INSERT INTO user VALUES ('Kanzaria', 'python', 25)
    END

Struggling for so long. Kindly help. Thanks!!

Answer 1

MySQL doesn't permit if logic, unless you are in a programming block (stored procedure, trigger, or function).

Fortunately, you can do the same with WHERE logic:

INSERT INTO user 
    SELECT 'Rutvij', 'python', 25
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python')
    UNION ALL
    SELECT 'Kanzaria', 'python', 25
    FROM DUAL
    WHERE EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');

MySQL should process the SELECT before the INSERT, so only one row should be inserted.

Or, you can do this as two INSERTs but in the opposite order:

INSERT INTO user 
    SELECT 'Kanzaria', 'python', 25
    FROM DUAL
    WHERE EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');

INSERT INTO user 
    SELECT 'Rutvij', 'python', 25
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');