Custom Search
 


How to disable (or enable) Safe Updates in MySQL


When you do action queries such as update or delete in MySQL Workbench (or any other MySQL client), if you are not able to update or delete records in a table, it could be caused by the default safe mode in MySQL.

Here is the error code:

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

It's understandable MySQL tries to be safe to avoid accidental sweeping update/delete. The error persists even when you do have WHERE clause in your query.

To fix the problem, turn off the safe mode by using following code for current connected session.

SET SQL_SAFE_UPDATES=0;

You also can disable safe mode in MySQL Workbench, go to Edit -> Preferences -> SQL Editor, and uncheck "Safe Updates" check box. Then reconnect to MySQL server by going to Query -> Reconnect to Server.

To turn safe mode back on, use the following code.

SET SQL_SAFE_UPDATES=1;

When enabled, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows.

The best practice is to enable safe updates in MySQL Workbench's Preferences, then when you need to do update or delete, turn off safe updates by issuing command SET SQL_SAFE_UPDATES=0; for current session.

Happy Coding!



Other tutorials in this category

1. Create MySQL table by using CREATE TABLE statement

2. Create MySQL table by using another table

3. Create MySQL temporary table

4. MySQL constraints explained by examples

Back to Tutorial Index Page


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy