Search code examples
mysqlmysql-error-1064strcmp

Why does this strcmp() syntax give me an error in mysql-5.7?


I was trying to test out the strcmp() function with strings containing random email addresses in this SQL statement:

INSERT IGNORE INTO possible_duplicate_email
-> (human_id, email_address_1, email_address_2, entry_date)
-> VALUES(LAST_INSERT_ID(), 'bobyfischer@mymail.com', 
                                   'bobbyfischer@mymail.com')
-> WHERE ABS( STRCMP('bobbyrobin@mymail.com', 'bobyrobin@mymail.com') ) = 1;

Then I got this error message:

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 'WHERE ABS( STRCMP('bobbyrobin@mymail.com', 
'bobyrobin@mymail.com') ) = 1' at line 4

I've read the strcmp() documentation from the mysql-5.7 reference manual and tried a simple SELECT statement to see that strcmp() returned a numeric value ranging -1, 0, 1, which it did, and I've included the IGNORE option to the SQL statement so as to proceed through errors. Could someone explain to me why I get this error when running strcmp() in the WHERE clause?


Solution

  • Thanks to Paul T., the answer is:

    INSERT statements do not have a WHERE clause, unless doing an INSERT ... SELECT statement, then the SELECT portion of the query can use a WHERE clause.