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?
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.