MySQL MariaDB

What is safe UPDATE mode in MySQL

MySQL is an RDMS that manages the data by arranging them in the tables, it uses the SQL query language to insert or update the data in the table but not every time it allows you to make the changes in the tables using the commands. If the safe UPDATE mode is enabled, MySQL does not run the UPDATE or DELETE if you try to execute them without a WHERE and LIMIT statement also if there is no condition with the key column.

The safe UPDATE mode in MySQL

To understand safe UPDATE mode, we will consider a table, students_data, and display it using the command:

SELECT * FROM students_data;

If we try to make changes either by updating the data or deleting the data it will generate an error because by default the safe UPDATE mode is always enabled, to check it we will make update the value of “Paul” to “Tom” using the command:

UPDATE students_data SET St_Name='Tom' WHERE St_Name='Paul';

It can be seen from the output that an error has been generated of the safe update mode, which means safe mode has been enabled and it will not allow us to make any changes in the table; to make changes we have to first, disable the safe update mode.

How to disable the safe UPDATE mode in MySQL

We can disable the safe UPDATE mode in MySQL, if we want to make some changes in the tables, to do so run the following command:

SET SQL_SAFE_UPDATES=0;
[/c]c
<img class="wp-image-137634" src="https://linuxhint.com/wp-content/uploads/2021/11/word-image-762.png" />

After disabling the safe UPDATE mode, again run the previous command to change the name of “Paul” to “Tom” using the command:
[cc lang="mysql" width="100%" height="100%" escaped="true" theme="blackboard" nowrap="0"]
UPDATE students_data SET St_Name='Tom' WHERE St_Name='Paul';

The table has been updated successfully, to display the table:

SELECT * FROM students_data;

How to enable the safe Update mode in MySQL

In order to again enable the safe Update mode in MySQL, execute the following command:

SET SQL_SAFE_UPDATES=1;

To verify that the safe UPDATE mode has been enabled, we will change the “Houston” city to “Paris”, using the command:

UPDATE students_data SET St_City='Paris' WHERE St_City='Houston';

The safe UPDATE mode has been successfully enabled.

Conclusion

MySQL is a well-known database management system that offers many features to its users which help them to enhance the performance of their tasks. In this write-up, we have discussed the safe UPDATE mode in MySQL through which we can restrict the users to update the tables by enabling or disabling the safe UPDATE mode. In this guide, we discussed both scenarios of enabling and disabling the safe UPDATE mode and checked its impact on the update command.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.