MySQL updates incorrectly


#1

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!


#2

What does the BINARY do in the WHERE clause?


#3

It enables an exact string match so that “Mary” doesn’t match with “mary”.


#4

Really strange. There shouldn’t be anything wrong with the SQL statement. Did you try it with another client, such as heidisql? What happens with the datasets then?

What I do in such cases, I copy the code and wrap the complete SQL-Statements outside the cfquery (that means isolatetd and wrapped in a cfoutput tag only), just to see all the variables cf is dumping into the SQL Statement. That cfoutput I can copy and simply paste into the heidisql client afterwards. Just to check.

Another question: You are updating a userID, as a string? Usually IDs are numbers, as being a type of unique number to use as primary key in a user table. But this depends on your db design of course. Since user names are not unique, you could come into updating several datasets.

It should be something like:

UPDATE prodndb.tblReceiptDetails SET tblReceiptDetails.UserName = ‘exEmployee’ WHERE BINARY tblReceiptDetails.UserName=’#uName#’ and tblReceiptDetails.UserID=’#uID#’


#5

andreas, thanks for the response and you are correct in that there shouldn’t be anything wrong with the SQL and that with a name like UserID you would usually expect a number but in this case it is actually a string. I’ve found the problem and it’s the nut on the end of the keyboard! The program I wrote to detect users who were not in the master user table was faulty and didn’t show that, for instance" the table tblReceiptDetails already contained entries for user “fred” and that those which contained “Fred” were correctly changed to “exEmployee”. I shall now retreat and continue with my meal of humble pie.


#6

Good news that you fond out what was causing your issue!!! Have a good weekend.