While using MySQL Workbench, we tried to execute an UPDATE command that did not use the Key of the table in the WHERE clause. In our case, it did not matter and it was expected to perform that update without that restriction.
When we tried to execute the code though, we got the following 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
and the following solution from Workbench:
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
We did not want to close the session for many reasons (we had data in other tabs, we would lose the undo history, we would have to revert the change later on, etc.).
To solve this issue without reconnecting nor changing the configuration of the tool, we appended the following code above the update statement and executed both lines together (we selected the two commands and pressed CTRL+Shift+Enter):
SET SQL_SAFE_UPDATES = 0;
This line of code, instructed the tool to ignore Error 1175 and allowed us to complete our task.
This post is also available in: Greek
SET SQL_SAFE_UPDATES=0;
Is not neccessary and can be unintentionally destructive. I learned the following while researching this concern…
4.5.1.6.4 Using the –safe-updates Option
Source: https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html
You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both).
For example:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;
The LIMIT clause works just fine and avoids the risk of using DROP unintentinally when disabling SQL_SAFE_UPDATES in the query source makes possible.
go to edit menu select preferences then click on preferences
after select SQL Editor
then uncheck the safe update check box at the last and click ok button and close MySQL server workbench 8.0 CE open MySQL server workbench 8.0 CE and then run the update command
Otherwise use MySQL 8.0 Command Line Client Mode.