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!
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.
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( …”
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.
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
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