Mysql DELETE FROM with subquery as condition

Workaround strategy

Watch out for MySQL's restrictions saying "No touching the table while it's still in use!" Fortunately, with some sneaky strategies, we can navigate around these limitations.

Creating a Temporary Table:

"Why not Zoidberg?" Err... I mean, why not a temporary table? You can elegantly dodge the hurdles by first putting to-be-deleted records into a breather space - a temporary table - and then performing deletion using this table.

CREATE TEMPORARY TABLE temp_table AS
SELECT id FROM main_table WHERE condition;

DELETE FROM main_table WHERE id IN (SELECT id FROM temp_table);

DROP TEMPORARY TABLE IF EXISTS temp_table;


Nested Subquery-cum-LEFT JOIN:

Got some records refusing to match? A LEFT JOIN with a nested subquery will help you pick non-matching records, allowing deletion against a condition that sprawls across multiple tables:

DELETE m 
FROM main_table m
LEFT JOIN (
  SELECT id FROM other_table WHERE condition
) o ON m.id = o.id
WHERE o.id IS NULL; -- sorry buddy, lonely NULLs have to go :-(


Sage spells from SQL wizards

Deleting artfully requires you to maintain parent-child relationships. Don't leave any lonely child records orphaned!

Parent-Child Deletion:

Remember, families should stick together. So, always make sure you're deleting from child tables before parent tables to uphold referential integrity:

DELETE child 
FROM child_table child
INNER JOIN parent_table parent ON child.parent_id = parent.id
WHERE parent.condition; 
-- Rule of thumb: always clean up after your kids

DELETE FROM parent_table WHERE condition; 
-- Time for the parents to relax


Ensuring Data Type Harmony:

If you're executing operations based on backup tables, ensure the data types match. No one likes surprises (the bad kind, anyway):

SELECT * FROM backup_table WHERE CAST(column AS the_same_data_type) = target_value; 
-- C'mon guys, we're all integers here!


Cross-Check Subquery:

Always run the subquery separately before including it in the DELETE query. Better safe than sorry!

SELECT * FROM (SELECT id FROM main_table WHERE condition) AS verification_subquery;
-- Test drive before the actual race


Conquering complex conditions

Contrary to what your horoscope says, battling complex deletion conditions is achievable. Hold your SQL wand tightly and conjure up some intricate structures:

Subquery Tackling Multiple Conditions:

DELETE FROM main_table WHERE id IN (
  SELECT id FROM (SELECT id FROM main_table WHERE condition1 AND condition2) AS subtable
);
-- 🔥 SQL spell of multiplicity 🔥


Alias and Join Method:

Here's a cool trick: Use an alias for the main table within the subquery, and join the two. Yes, you're allowed to call GameObject by a pet name!

DELETE m 
FROM main_table AS m
JOIN (
  SELECT id FROM main_table WHERE condition
) AS subquery ON m.id = subquery.id;
-- Meet subquery, my twin!
