MySql Error Code: 1175. You are using safe update mode - Even with primary key

user_1283672 0
Viewed 2686

I have the following query:

UPDATE borsalino_db.reservations SET confirmed=if(confirmed=1, -1, 0) WHERE confirmed = 1;

This produces the error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Only when i add

SET SQL_SAFE_UPDATES = 0;
UPDATE borsalino_db.reservations SET confirmed=if(confirmed=1, -1, 0) WHERE confirmed = 1;
SET SQL_SAFE_UPDATES = 1;

It works. But this seems wrong to me to do it like this. I do not want to disable safe mode. So i tried to add confirmed as a primary key but still getting the error.

enter image description here

Any ideas on how to solve this? Thanks

mysql
1 Answers

Well a work around that, is to use something that can never fail in your where clause for example:

WHERE confirmed = 1 AND id > 0

That way you will use a key column and id is always above 0 so you never gonna run into problems. It's just a work around to get over this issue but i believe that your solution is still the most common one and the most used.

To be honest i believe that your solution in your question is the real way to go but since you asked for an alternative the above way may fit you as well.

user_75455550
Related