Extremely simple IF EXISTS statement failing?

Hey there! I have this bafflingly simple SQL statement that is failing and I can’t seem to figure out why.

I have a database called ‘mytable’ that has three rows labeled ‘row’, ‘description’, and ‘model_#’. The table is empty of any entries.

I try running the statement IF EXISTS (SELECT * FROM myTable WHERE description = 'blah') SELECT * FROM myTable;

Obviously this doesn’t do anything useful, it is just to show that the “IF EXISTS” combo seems to be broken. Every time I try and run the above statement it returns:

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 'IF EXISTS (SELECT * FROM myTable WHERE description = 'blah') SELECT * FROM myTab' at line 1

No variation of this IF EXISTS statement seems to be working. What am I doing wrong? Does it have something to do with the fact that I am using MySQL? Thanks a lot!

I’ve not used IF EXISTS myself, but maybe this will answer your issue.

bingo

you cannot use IF outside of a function

If that is the case, why do other people use it in tutorials online?

Does it have something to do with using MySQL specifically versus another db platform like Microsoft’s? That’s only a theory, I haven’t found anything online that says that would be the case.

Yup.

Gotcha. In that case, what’s the best way to do an IF x EXISTS THEN UPDATE ELSE INSERT?

I’ve tried playing around with things like they used in the aforementioned StackOverflow answer but I don’t know how to combine the IF into an UPDATE and INSERT. I just keep getting database errors when I try to do anything other than “SELECT IF( …”

By following the manual and use the option available with MySQL, more specifically insert on duplicate update.

Take a look here for more information
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

4 Likes

Okay, that seems perfect, like exactly what I’m looking for! The only problem is, when I run that command on my database it gives the same error:

INSERT INTO mytable (row, description, model) VALUES (0, "hinges", "AAB1") ON DUPLICATE KEY UPDATE mytable SET description="hinges", model="AAB1" WHERE row=0;

returns

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 'SET description="hinges", model="AAB1" WHERE row=0' at line 1

As far as I can tell it is all valid syntax, according to the link you sent? It also seems weird that the error seems to be getting hooked on the “SET” part, but even when I change stuff after the “SET” it doesn’t fix the error.

What should I do?

Read the information in the MySQL manual that I linked to above. There you will see the correct syntax for the update duplicate part of the query.

I did read the manual you linked above and used the correct syntax that they used in the manual.

If you are referring to the fact that in the example statement above I included a “WHERE” clause and a “SET”, then that is my mistake because I did try it without the WHERE and SET and with only one field being UPDATEd. When that (and other variations) failed, the last statement I attempted was the one I wrote above, but I suppose I should have shown the first statement I tried, not the last, so sorry if that caused confusion.

As I was typing this response, though, I realized I made a small typo in my statement. So that’s why it wasn’t working. The ON DUPLICATE KEY UPDATE works now :sweat_smile:

Thanks so much for the help! This is exactly what I needed to know to solve my problem!

precisely – you placed your table name and the SET keyword in between the UPDATE keyword and the column assignments, when in fact there should be nothing there

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.