HANDLING THE SAFE UPDATE MODE IN MYSQL

*Safe Update Mode is a feature designed to prevent unintentional data loss or corruption during update and delete operations. It restricts the execution of UPDATE and DELETE statements that don't include a WHERE clause that uses a key column, thus preventing accidental modifications to entire tables. *

The Default and Safe Mode

• When SQL_SAFE_UPDATES is set to 1 (which is the default setting in many MySQL client tools like MySQL Workbench), the database system will prevent UPDATE or DELETE statements that:
• Do not use a WHERE clause: For example, UPDATE my_table SET column1 = 'new_value';
• Do not use a key column in the WHERE clause: For example, if id is your primary key, UPDATE my_table SET column1 = 'new_value' WHERE some_other_column = 'value'; would be blocked unless some_other_column is also indexed or part of a unique key.

SET SQL_SAFE_UPDATES = 1
DELETE FROM database_name.table_name
WHERE condition;

The Unsafe/ Override Mode

• When SQL_SAFE_UPDATES is set to 0, the database system will not prevent UPDATE or DELETE statements that lack a WHERE clause or don't use a key in the WHERE clause.

• This is typically used when you intentionally want to perform a mass update or delete operation without specifying a key column in the WHERE clause, or if you are sure about your WHERE clause, even if it doesn't involve a key. It gives you more flexibility but also removes the safety net.

SET SQL_SAFE_UPDATES = 0
DELETE FROM database_name.table_name
WHERE condition;