0

i´m traying to update a column, but during update always have the same mistake, how can i solve this if i dont want to disable "safe update mode? This is my query

UPDATE test
SET unit_price= (SELECT unit_price*1.15
                   FROM (SELECT unit_price  from test )as l           
                  )where qtyinstock>0

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. 0.000 se

Ken White
  • 123,280
  • 14
  • 225
  • 444
Elohim Miranda
  • 1
  • 2
  • Please include the full, exact error message you're getting, rather than just the error number. We shouldn't have to search for the error code to find out what it means, and the contents of the error message often provides information that is useful in solving the problem. The full error message is on the screen right in front of you, so there is absolutely no reason for you not to provide it in your question. You're asking us for free help to solve your problem, and it's in your best interest to make it as easy as possible for us to do so by giving us the information you already have. – Ken White Oct 02 '21 at 01:40
  • Also, your experiences here will be much better if you spend some time taking the [tour] and reading the [help] pages to learn how the site works. – Ken White Oct 02 '21 at 01:41
  • The error message tells you how to fix it. Use a WHERE that uses a KEY column. It would help greatly if you added the CREATE TABLE DDL for the table and some sample data. – Ken White Oct 02 '21 at 02:00
  • This is a feature to prevent beginners to make mistakes (e.g. updating all rows by accident instead of a specific one - identified by the primary key), see e.g. [here](https://stackoverflow.com/q/11448068). MySQL Workbench, which you are probably using, enables this mode by default. The error message tells you how to disable that "safe mode" in the Workbench settings: *"To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect."* (If you are using a different editor, either tell us or look for a similar option there). – Solarflare Oct 02 '21 at 07:22
  • thanks all the comments was helpfull. – Elohim Miranda Oct 04 '21 at 17:16

1 Answers1

0

I have been fighting with this exact problem for a couple weeks. In my case, the UPDATE code works some days and fails with Error 1175 on others even though I do specifically call out the primary table index in my query where clause. It seemed so random and was extremely frustrating and confusing.
There are so many comments about disabling safe update mode but that's just not an acceptable option in my case. IMHO, data integrity is too important to disable this feature, we are all human and we all make mistakes. So after a lot of research I realized that the MySQL query optimizer was making choices about which index to use and when it decided to use an index other than the primary it will throw this error message. Some reading linked at the bottom.

For my implementation the solution was to simply add an optimizer hint to the Update statement:

Update salesFact FORCE INDEX(PRIMARY)
...

Was actually this simple in my case! In my case I had to use Force Index instead of Use Index. Performance does not seem to be negatively affected either. I hope this helps others as it's taken a couple weeks of my life away! ;)

Read about safe-update mode: https://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html Read about optimizer hints: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

Mick
  • 1
  • 3