Delete all rows in a table based on another table

Let's rehearse before playing live!

Always preview your action before hitting the big red button! With SELECT you can see the rows earmarked for the Infinity Gauntlet snap:

SELECT target.*
FROM table_to_delete target
INNER JOIN reference_table ref ON target.id = ref.id;


Deleting rows - The alternatives manual

JOINs aren't everyone's cup of tea. There are many routes to Destination Deletion, and here are some popular ones:

The Spy who deleted me - WHERE IN

DELETE FROM table_to_delete 
WHERE id IN (SELECT id FROM reference_table); -- spy and destroy


Mission Exists: WHERE EXISTS

DELETE FROM table_to_delete t1
WHERE EXISTS (SELECT 1 FROM reference_table t2 WHERE t1.id = t2.id); -- defiance against existence


PostgreSQL Only: DELETE using USING clause

DELETE FROM table_to_delete t1
USING reference_table t2
WHERE t1.id = t2.id; -- Drop the hammer, PostgreSQL Stylez


Also, remember to benchmark your queries. Just because function A outraced function B down the Autobahn, doesn't mean the same will happen on your potato PC.

Life jackets and parachutes: Backups and Transactions

Before we happily hurl rows into oblivion, backup your data and use transactions. Now, you're protected, like a SQL Avenger:

BEGIN TRANSACTION;
-- Your DELETE operation
ROLLBACK TRANSACTION; -- Ignore the big red button press
-- COMMIT TRANSACTION; -- Proceed with Project Thanos


And for bonus points, double-check your aliases and JOIN conditions, nobody wants an unscheduled trip to Snapsville!

Beyond the Basics: Tips, tricks and gotcha's

Performance Tuning: Indexes and Constraints

You wouldn't enter a F1 race without a race car, right? Likewise, never go into a big delete operation without proper indexes:

CREATE INDEX idx_reference_id ON reference_table (id);


Context Matters: Syntax Variations

Query syntax varies across platforms, so always adapt if necessary. For example, the SQL Server version of our JOIN deletion looks like this:

DELETE t1 
FROM table_to_delete AS t1 
JOIN reference_table AS t2 ON t1.id = t2.id; -- SQL Server styled party crashing


Test Drives: Use a Test Environment

Always test your queries in a sandbox before letting them loose in the wild. Your bosses will appreciate that!

The Guard at the Door: User Permissions

Finally, ensure that you have the appropriate permissions. Remember, with great power, comes great, umm...access!

---

The droids you're looking for : Verifying JOIN conditions

Double-check your JOIN conditions. Use a SELECT statement before the delete operation to ensure you're targeting the right "droids":

SELECT target.id, ref.id
FROM table_to_delete target
INNER JOIN reference_table ref ON target.id = ref.id;


A need for speed : Think Performance

Performance matters! Remember, not all delete methods are created equal. Depending on your specific situation, some operations may run like Quicksilver, others like molasses uphill.