MySQL ERROR code 1175 is triggered when you try to update or delete a table data without using a WHERE clause.
MySQL has a safe update mode to prevent administrators from issuing an UPDATE or DELETE statement without a WHERE clause.
You can see if safe update mode is enabled in your MySQL server by checking the global variable sql_safe_updates.
Check the global variable using the SHOW VARIABLES statement as follows:
SHOW VARIABLES LIKE "sql_safe_updates";
-- +------------------+-------+
-- | Variable_name | Value |
-- +------------------+-------+
-- | sql_safe_updates | ON |
-- +------------------+-------+
The example above shows that sql_safe_updates is turned ON, so an UPDATE or DELETE statement without a WHERE clause will cause the error 1175.
Here’s an example UPDATE statement that causes the error:
mysql> UPDATE books SET title = "Stardust";
ERROR 1175 (HY000): You are using safe update mode and you tried
to update a table without a WHERE that uses a KEY column.
To fix the error, you can either disable the safe update mode or follow the error description by adding a WHERE clause that uses a KEY column.
You can use the SET statement to disable the safe update as shown below:
SET SQL_SAFE_UPDATES = 0;
Now you should be able to execute an UPDATE or DELETE statement without a WHERE clause.
If you want to turn the safe update mode back on again, you can SET the global variable to 1 as shown below:
SET SQL_SAFE_UPDATES = 1;
If you’re using MySQL Workbench to manage your database server, then you can disable the safe update mode from the Preferences menu.
Click on Edit -> Preferences for Windows or MySQLWorkbench -> Preferences for Mac.
Then click on SQL Editor tab and uncheck the Safe updates feature:
Keep in mind that updating or deleting a table without a WHERE clause will affect all rows in that table.
The safe update mode is added to prevent accidental change that can be fatal.
It’s okay if you’re testing with dummy data, but please be careful with real data 👍