Last Updated on Friday, 11 January 2013
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.
Latest Posts
-
Joes Revolver Map (JRM)
-
Fri 17-May-13
Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
-
Fri 17-May-13
Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
-
Fri 17-May-13
Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
-
Fri 17-May-13
Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
-
Fri 17-May-13
Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
-
Fri 17-May-13