MySQL Toggle Safe Update Mode

This article assumes you are aware of what you want to achieve. It is recommended to backup your database before making bulk updates.

In this article we will take a look at what is “Safe Update Mode” in MySQL and how to toggle it.

What is Safe Update Mode?

Safe Update Mode in MySQL is an option that does not allow executing UPDATE or DELETE statements without a WHERE clause. Its main objective is to avoid deleting or updating large numbers of rows by mistake.

If this mode is active you may encounter an error 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. To disable safe
mode, toggle the option in Preferences -> SQL Editor and reconnect.

How to toggle Safe Update Mode

If you know what you are doing and you are sure you want to update multiple rows at once, you can use execute multiple statements to disable safe update mode temporarily, execute the code and enable it back.

SET SQL_SAFE_UPDATES = 0; #Disable Safe Update Mode
UPDATE `table` SET `column` = 1 WHERE `column` IS NOT NULL; #Update Table
SET SQL_SAFE_UPDATES = 1; #Enable Safe Update Mode

Tags:

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.