If Not Exist Not Working

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

Solution 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:

INSERTINTOuserSELECT'Rutvij', 'python', 25FROM DUAL
    WHERENOTEXISTS (SELECT1FROMuserWHERE name ='Rutvij'AND lang ='python')
    UNIONALLSELECT'Kanzaria', 'python', 25FROM DUAL
    WHEREEXISTS (SELECT1FROMuserWHERE 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:

INSERTINTOuserSELECT'Kanzaria', 'python', 25FROM DUAL
    WHEREEXISTS (SELECT1FROMuserWHERE name ='Rutvij'AND lang ='python');

INSERTINTOuserSELECT'Rutvij', 'python', 25FROM DUAL
    WHERENOTEXISTS (SELECT1FROMuserWHERE name ='Rutvij'AND lang ='python');

Solution 2:

This is not a query, this would be an sql script with control flow logic, which is not allowed in mysql outside stored programs (procedures, functions, triggers). Even if you encapsulated the above code into a stored procedure it would not work because exist / not exists can only be used in subqueries.

I would do the following:

1. Create a stored procedure
2. Declare an integer variable
3. Using select into fetch the count of rows where name='Rutvij' AND lang='python' into your variable.
4. Use the if statement to do the insertion based on the number of records.