Why does MySQL think that I'm not using a WHERE statement?

Question

I'm trying to update a table called rep in a database called premier_products. The table's primary key is rep_num.

When I run the following statement:

update rep 
set last_name = "Perry"
where rep_num = 85;

I get an error that says "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column."

I Googled that error message and most of the responses were along the lines of "You have to use a where clause or turn off safe mode". But as you can see, I am using a where clause. Why is the error appearing if I have a where clause?

MySQL server version 5.6.20.

This image shows that rep_num is definitely my primary key:

This image shows the current rep table:

`rep_num` is prob not indexed. Either give it one or change your settings in workbench to remove that restriction. — John Conde, Nov 09 '14 at 23:10
You sure `rep_num` is a primary key? What does your table creation script look like? — Phil, Nov 09 '14 at 23:13
@John - What do you mean by not indexed? Here's a picture of the table, if it helps: http://i.imgur.com/TSyZ6AX.png — Pikamander2, Nov 09 '14 at 23:15
Here is the script that was used to create it, if that's what you're asking: http://i.imgur.com/pKqVUD8.png — Pikamander2, Nov 09 '14 at 23:19
Yeah, and if you look at this (http://i.imgur.com/xbuICZ6.png), it appears that rep_num is still the primary key. — Pikamander2, Nov 09 '14 at 23:20
Possibly, but probably not, related: http://bugs.mysql.com/bug.php?id=70472 — Lightness Races in Orbit, Nov 09 '14 at 23:27
`SET SQL_SAFE_UPDATES=0;`, found [here](http://stackoverflow.com/questions/14231278/error-error-code-1175-during-executing-update-command-on-table-using-mysql-wo#answer-14231331) — skobaljic, Nov 09 '14 at 23:43
@skobaljic- I know how to make the message go away, but I want to know why it's appearing in the first place. — Pikamander2, Nov 09 '14 at 23:45

score 1 · Accepted Answer · answered Nov 10 '14 at 14:28

Although you save only numbers, your primary-key type is char(2) and not tinyint(2) and when you update the record you are giving numerical value instead char value in your where condition. I think thats where the indexing mechanism triggers the error and tells you, your where condition is unsafe or might yield 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? like tbl_rep? just a thought.

Thanks, that fixed it. It's part of a homework assignment, so I have no control over the names. — Pikamander2, Nov 10 '14 at 20:37

Why does MySQL think that I'm not using a WHERE statement?

1 Answers1