Today, I encountered an error code 1175 while deleting the MySQL table row using MySQL Workbench. The error was like:
delete from mytablename where mycolum='myvalue' 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.265 sec
This is because my MySQL server has a safe-update option enabled.
As per the MySQL documentation on safe update, we can’t DELETE or UPDATE records without specifying KEY in the WHERE clause.
For example:
We have a table student and it has a primary key rollNo. Then, if we try to use DELETE or UPDATE query without specifying rollNo key in WHERE clause it does not allow us to modify the data.
Hence, to solve this issue we need to disable the safe update mode before executing the query.
Query to disable:
SET SQL_SAFE_UPDATES = 0;The above query disables the safe mode option and it allows us to execute query even if we are not specifying the key in WHERE clause.
To make it safe, we can again enable safe mode after executing our DELETE or UPDATE query.
Query to enable:
SET SQL_SAFE_UPDATES = 1;