Delete related records from multiple tables

What?
So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN statements.

Why?
It is never recommended to delete from multiple tables and instead to use the system you were given. Given a Relational Database Management System (RDBMS) where deleting some erroneous rows in one table will cause errors because the existence of those rows is stored in another table, how do we delete from all the related tables as well?

How?
You need to refer to the tables just after the DELETE clause (so instead of DELETE FROM myTable WHERE myValue=givenValue):
DELETE s, ssf, sst
FROM `Session` s 
LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId
LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId
WHERE s.dateTime >= '2013-02-01 00:00:00'

-- Deletes all sessions dated after February 1st 2013 
-- and all related records (linked by ID) in relevant tables.

Delete from specific tables but not all
Simply don't refer to the table after the DELETE clause. Rather straightforward but I got this answer from StackOverflow. Based on the previous example, here's the query to only delete records in Session and SessionStudent represented by aliases s and sst respectively and to leave SessionStaff table unchanged:
DELETE s, sst
FROM `Session` s 
LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId
LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId
WHERE s.dateTime >= '2013-02-01 00:00:00'

Testing first
I should put this at the beginning of the article as it is what you should do before running a DELETE FROM statement but I've added it here as I use it quite often:
SELECT 
     s.sessionId, 
     ssf.sessionId, 
     sst.sessionId
FROM `Session` s 
LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId
LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId
WHERE s.dateTime >= '2013-02-01 00:00:00'

-- yields:
sessionID  sessionID  sessionID
---------  ---------  ---------
148797	   148797	148797
148798	   148798	148798
148799	   (null)	148799
148800	   148800	148800
148686	   148686	(null)

-> The select here returns 5 rows
-> A delete here would affect 13 rows.
This should show all records to be deleted.

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.