Tuesday, March 31, 2015
   
Text Size
Login

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):

  1.  DELETE s, ssf, sst 
  2.  FROM `Session` s  
  3.  LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId 
  4.  LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId 
  5.  WHERE s.dateTime >= '2013-02-01 00:00:00' 
  6.   
  7.  -- Deletes all sessions dated after February 1st 2013  
  8.  -- 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:

  1.  DELETE s, sst 
  2.  FROM `Session` s  
  3.  LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId 
  4.  LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId 
  5.  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:

  1.  SELECT  
  2.       s.sessionId,  
  3.       ssf.sessionId,  
  4.       sst.sessionId 
  5.  FROM `Session` s  
  6.  LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId 
  7.  LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId 
  8.  WHERE s.dateTime >= '2013-02-01 00:00:00' 
  9.   
  10.  -- yields: 
  11.  sessionID  sessionID  sessionID 
  12.  ---------  ---------  --------- 
  13.  148797        148797     148797 
  14.  148798        148798     148798 
  15.  148799        (null)     148799 
  16.  148800        148800     148800 
  17.  148686        148686     (null) 
  18.   
  19.  -> The select here returns 5 rows 
  20.  -> A delete here would affect 13 rows. 
This should show all records to be deleted.
Add Comment

Name:

Email:

Website:

Message:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Sat 28-Mar-15
      Hi Vsiqueira, I used to get a similar problem if I switched my android device off and on again.
      Joel L.  
    • Fri 27-Mar-15
      hi, im getting an erro, in the method MyLocation.getL ocation(), return null, how i can resolve ...
      vsiqueira  
    • Wed 25-Mar-15
      Hi Rishikesh, I use a database on a webserver with all the latitudes and longitudes of the points of ...
      Joel L.  
    • Tue 24-Mar-15
      At the starting asa I run the app. I want to show list of garages available near by my current location.
      Rishikesh
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Mon 23-Mar-15
      Worked like a charm first time! :lol: Getting bored of the spam comments. This should stop them. :P
      Graham