Why does MySQL think I am not using the WHERE clause?
I am trying to update a table called rep
in a database called premier_products
. The primary key of the table rep_num
.
When I run the following statement:
update rep
set last_name = "Perry"
where rep_num = 85;
I get the error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column."
I realized that the error message and most of the answers were on the lines "You should use the suggestion where
or disable safe mode". But as you can see I am using the suggestion where
. Why is there an error if I have a suggestion where
?
MySQL server version 5.6.20.
This image shows that rep_num is definitely my primary key:
This image shows the current rep table:
source to share
Even though you only store numbers, your primary key type is char (2), not tinyint (2), and when you update the record, you are assigning a numeric value to char in your state. I think that's where the indexing engine throws an error and tells you that your condition is unsafe or might lead to wrong results.
in your case try
update rep
set last_name = "Perry"
where rep_num = '85';
PS: why don't you name your tables with a prefix? how is tbl_rep? just a thought.
source to share