Mysql delete under safe mode

sql

temporary-tables

subqueries

mysql-workbench

byAlex Kataev·Oct 26, 2024

To delete records under MySQL's safe mode, the quickest way is to temporarily turn off safe mode, precision-target the entries to delete based on a unique key, limit the count, and reactivate safe mode afterward:

SET SQL_SAFE_UPDATES = 0;  -- Shutting off the safety. Buckle up!
DELETE FROM table_name WHERE unique_key = value LIMIT 1;  -- Precision strike.
SET SQL_SAFE_UPDATES = 1;  -- Safety back on! Phew, that was thrilling.

Find and replace table_name, unique_key, and value with your own variables, and carefully set the LIMIT to control the number of deletions.

Delete with temporary table

When it's non-feasible or risky to tweak SQL_SAFE_UPDATES, using temporary tables could be your knight in shining armor. This involves creating a temporary table with the results of a subquery, then deleting from the target table using IDs from your temporary grab-bag (table).

CREATE TEMPORARY TABLE temp_ids AS
SELECT id FROM instructor WHERE condition_to_delete;  -- Round up the usual suspects!

DELETE FROM instructor WHERE id IN (SELECT id FROM temp_ids);  -- Off with their heads!

DROP TEMPORARY TABLE temp_ids;  -- Cleaning up the aftermath.

Avoid "You can't specify target table for update in FROM clause" error by executing your plan in multiple steps. But still, beware of unintended deletions from misspecified WHERE conditions.

Subqueries to the rescue

If you can make MySQL believe that all rows are in danger using a non-existent condition (like id <> 0 on a primary key), you can convince it to satisfy the safe update requirement. In reality, this crafty method risks no data:

DELETE FROM instructor WHERE id = ANY (SELECT id FROM (SELECT id FROM instructor WHERE id <> 0) AS subquery);  -- Works every time.

Aliases for tables in the subquery realm present an effective workaround for MySQL's "You can't specify target table" chaff.

No safety within MySQL Workbench

If you're more of a MySQL Workbench person, you can bypass safe mode from within its preferences: Edit → Preferences → SQL Editor → Other. Unchecking "Safe Updates" faces risks, so tread carefully.

Deletion playground with SQLFiddle

Those who fear executing SQL commands on live databases could turn to SQLFiddle for a practice run. They've got plenty of demos for a hands-on understanding.

Complex deletions simplified

Have a headache dealing with complex deletion criteria? Ease your pain with temporary tables, aliased subqueries and insurance-adding WHERE conditions. Here's a pill for your ailment:

DELETE a FROM instructor AS a
JOIN (SELECT id FROM instructor_data WHERE condition_to_join) AS b
ON a.id = b.id WHERE complex_condition;  -- I bet you didn't see this coming!