Perform MySQL delete under safe mode?

To delete under safe mode, you can use the below query −

SET SQL_SAFE_UPDATES = 0;

To understand the above query, let us create a table. The following is the query to create a table −

mysql> create table SafeDeleteDemo
   −> (
   −> Price int
   −> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table with the help of insert command. The query is as follows −

mysql> insert into SafeDeleteDemo values(100);
Query OK, 1 row affected (0.11 sec)

mysql> insert into SafeDeleteDemo values(200);
Query OK, 1 row affected (0.19 sec)

mysql> insert into SafeDeleteDemo values(300);
Query OK, 1 row affected (0.09 sec)

mysql> insert into SafeDeleteDemo values(500);
Query OK, 1 row affected (0.14 sec)

mysql> insert into SafeDeleteDemo values(1000);
Query OK, 1 row affected (0.10 sec)

mysql> insert into SafeDeleteDemo values(150);
Query OK, 1 row affected (0.11 sec)

Display all records from the table before deleting records. The query is as follows −

mysql> select *from SafeDeleteDemo;

The following is the output −

+-------+
| Price |
+-------+
|   100 |
|   200 |
|   300 |
|   500 |
|  1000 |
|   150 |
+-------+
6 rows in set (0.00 sec)

Now delete under safe mode with the help of SET command. The query is as follows −

mysql> SET SQL_SAFE_UPDATES = 0;
Query OK, 0 rows affected (0.00 sec)

Begin with deleting some records the table now. We are in safe mode −

mysql> delete from SafeDeleteDemo where Price >=500;
Query OK, 2 rows affected (0.14 sec)

Now you can check how many records are present in the table after deleting records. The query is as follows −

mysql> select *from SafeDeleteDemo;

The following is the output displaying that we have successfully deleted records >= 500 −

+-------+
| Price |
+-------+
|   100 |
|   200 |
|   300 |
|   150 |
+-------+
4 rows in set (0.00 sec)

Now you can remove delete under safe mode with the help of the same SET command. The query is as follows −

mysql> SET SQL_SAFE_UPDATES = 1;
Query OK, 0 rows affected (0.00 sec)