In this article we will look at the meaning of following MySQL error, its reason and solution:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
Above error may arise
1. When deleting all records from a table with a DELETE FROM query that does not use a WHERE clause, or
2. When fetching a large data set using SELECT query that selects more than 1000 rows, or
3. Updating records with an UPDATE statement that does not use a WHERE clause.
MySQL server on which the query is being executed has
This option is enabled by default to prevent accidental deletion or updation of records. A
UPDATEstatement without a
WHEREclause deletes or updates all the records in a table.
To prevent this
--safe-updatesoption is enabled.
With this option enabled, MySQL will allow only those
UPDATEstatements that provide a condition using
WHEREclause and this condition should have the primary key of the table, so that all the records are not affected.
--safe-updatesmode, MySQL docs state
If this option is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error.
--safe-updatesis a security option and it should be turned on but many times you want to remove all the records from a table to start afresh and for this, a
DELETE FROM <table>statement needs to be issued.
--safe-updatesoption enabled, MySQL will not allow this. So, we need to disable it using any of the following ways.
1. Using query
Execute below query to turn off
set sql_safe_updates = 0;
Since it is a security feature, it is recommended to turn it on after the required query is executed.
So, it is better to wrap your SQL query between the two statements as shown below.
set sql_safe_updates = 0; -- your query set sql_safe_updates = 1;
2. Using not equal to
With this method, you are not required to disable
--safe-updates mode, you can create a query with
WHERE clause which returns
true for all records.
But remember, this
WHERE clause must contain the primary key for the table.
So, for deleting all the records of a table, use below query
DELETE FROM <table name> where id <> 0;
<> is not equal to operator and it will be true for all records since each will have an id other than 0.
3. From MySQL workbench
--sql-updates option can be turned off from MySQL workbench as well.
Click MySQLWorkbench menu item at the top(on Mac) and Preferences as shown below.
On Windows, it would be Edit -> Preferences.
Click on SQL Editor menu option in the window that appears. You will find Safe Updates as the last option on the right.
Look at the image below.
Uncheck and click OK.
Now execute the query, it should work.