Delete from two tables in one query

sql

transaction

foreign-key

join

byAlex Kataev·Nov 29, 2024

Here's how to delete records from two tables in MySQL or SQL Server in a single breath. Hold on to your keyboards!

In MySQL, you achieve this by using a JOIN within the DELETE statement, which empowers you to target multiple tables at once.

MySQL joint delete syntax:

// The bad news? Gotta delete. The bright side? Just one query!
DELETE t1, t2 
FROM table1 AS t1 
JOIN table2 AS t2 ON t1.id = t2.foreign_key 
WHERE condition;

Now, for SQL folks working with SQL Server or PostgreSQL, you'd perform the same action by sandwiching the delete statements between BEGIN and COMMIT in a TRANSACTION.

Transactional delete syntax:

BEGIN; 
// ROUND 1
DELETE FROM table1 WHERE condition; 
// AND ROUND 2, FIGHT!
DELETE FROM table2 WHERE condition; 
COMMIT; 
// And we all lived happily ever after.

Don't forget to replace table1, table2, id, foreign_key, and condition with your personal table scenario. Roll up those sleeves, start typing, and may the deletion force be with you.

Mastering multi-table deletions

When dealing with more than one table, apply TRANSACTION to block the operation and ensure data integrity. Basically, it's a "all or nothing" kind of deal.

Here is an example:

START TRANSACTION; 
// It's a tough job, but somebody gotta do it
DELETE FROM messages WHERE messageId = @messageId; 
// I hate to see you go, but I love to see you leave.
DELETE FROM usersmessages WHERE messageId = @messageId; 
COMMIT; 
// Sad, but true.¯\_(ツ)_/¯

Remember to use InnoDB for your tables. InnoDB is like a superhero, it supports transactions for you. Configure FOREIGN KEY constraints with ON DELETE CASCADE to set off a chain reaction of deletions and ensure referential integrity. Consider creating a sample set of data first to test your deletions. You don’t want to be "that guy" who accidentally deleted the entire data.

Going above and beyond with delete queries

LEFT JOIN, your left-hand man

LEFT JOIN is your trusty servant when you're dealing with unmatched rows. It makes sure no rows are forgotten, even if they don't have mates on the other table:

DELETE t1, t2
FROM table1 AS t1 
LEFT JOIN table2 AS t2 ON t1.id = t2.foreign_key 
WHERE condition;
// Ghostbusters for lonely rows

CLEAR: Check. CONCISE: Check. Readable with aliases!

If your statement looks like spaghetti code, dish out some aliases! It flavors up your query and makes it more readable.

DELETE msg, usrMsg
FROM messages AS msg 
JOIN usersmessages AS usrMsg ON msg.messageId = usrMsg.messageId 
WHERE condition;
// Who you gonna call? Alias-busters!

FOREIGN KEY constraints: Your delete assistant

Before getting your hands dirty with delete, ensure your foreign key constraints are in place. They prevent data mess and help automate deletions:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)
ON DELETE CASCADE;
// CASCADE: Because sometimes, change is good!

With these constraints, single delete in parent_table can clear out related records in child_table.

The delete target: Precision is key

Your join conditions should be as accurate as a sniper. They pinpoint the records that need to be deleted.

Practice safe deleting: Tips and tricks

Post-delete sanity check

After the carnage, ensure the areas are clear and only the intended victims fell. Use queries for a quick count of rows, check for orphaned records, or validate the data integrity.