I’m trying to update a field in my MySQL 5.7 db which has a foreign key relationship to the master User table. As User “Fred” has left the company, I need to change the User field to “exEmployee” for all his transactions before deleting him from the User table. So, I use the following (for one of the transaction tables) in a CFC:
UPDATE prodndb.tblReceiptDetails SET tblReceiptDetails.UserID = 'exEmployee' WHERE BINARY tblReceiptDetails.UserID = '#uName#'
The variable “uName” is set to “Fred” and the SQL finds several hundred entries changing them to “fred” - NOT “exEmployee” as requested. What am I doing wrong?
Curiously, if I try to change “jackm” to “exEmployee”, I get “JackM”?! Thinking this might be a server issue, I ran the SQL through MySQL workbench (v8.0) and it gives the same result!