Error Code: 1175, MySQL Safe Update Mode

Share and Enjoy !


Table of Contents

  1. Query
  2. Error
  3. What is Safe Update Mode?
  4. Solution
  5. MySQL Workbench
  6. Query
  7. Where clause

Today I was restoring the live WordPress site on the local machine for some development work. During the process, I execute some queries and got the following error, I am using MySQLWorkbench 8.0 and MySQL 8.0.23.


UPDATE ubupa.wp_options 
set option_value = replace( option_value, '', 'http://yyyyyy.lan')
	option_value like '' ;


Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

What is Safe Update Mode?

If you are a beginner then the Safe update mode is very useful for you. It is a client-side setting and default enabled mode in MySQL Workbench. As you understand by name, Safe Update Mode will prevent your UPDATE or DELETE query when you forgot the WHERE clause for a key column or LIMIT clause. 


Now we will see how we can solve it in all possible ways. It is not recommended to disable Safe update mode permanently. It is a security option and it must be turned ON but sometimes we need to delete all records and it will not allow us to do so. We will check how to disable it manually and in the query.

MySQL Workbench

We can turn it on and off safe mode in MySql Workbench as well. Let’s check, Go MySQLWorkbench >> Preferences >> SQL Editor, you will find —Safe Updates ( rejects UPATEs and DELETEs with no restrictions ). I unchecked it and click ok. 

Safe Update Mode
Workbench Preference

Now execute a query, it should work.


In this way, we will disable Safe Update Mode before our Query execution start but it is a security feature so we will enable it again after execution is completed.

Set sql_safe_updates = 0;

-- Our Update OR Delete Query --

Set sql_safe_updates = 1;

Where clause

In this method, we do not need to disable or enable safe update mode. So we will use the where clause in such a way, that will return true for all records. The only condition is we need a primary key column in the where clause.

UPDATE ubupa.wp_options 
set option_value = replace( option_value, '', 'http://xxxxxx.lan')
	option_value like '' AND option_id <> 0;

As you see we used the primary key column, option_id. Our where clause will return true as we do not have any records for id 0.

Download MySQL Workbench

70 / 100

Share and Enjoy !


Leave a Reply