Delete From Multiple Related Tables

[mjb3030]

I have four tables: Tests, Tires, Grooves, Points. They are related this way:

 

Tests to Tires: 1 to many: Tests.Test_ID = Tires.Test_ID

Tires to Grooves: 1 to many: Tires.Tire_ID = Grooves.Tire_ID

Grooves to Points: 1 to many: Grooves.Groove_ID = Points.Groove_ID

 

Each test may contain 16 tires. Each tire may contain 6 grooves. Each groove may contain 60 points. So, as you can see, if I want to delete one test along with all its related data, there could be a lot of records to delete. I would love it if I could do it all with one SQL statement rather than having to loop through every groove to get the points, every tire to get the grooves, etc.

 

I've been looking around for the answer, and this is what I have right now:

 

DELETE Points.*,Grooves.*,Tires.*,Tests.*
FROM Points INNER JOIN
(Grooves INNER JOIN
(Tires INNER JOIN Tests
ON Tires.Test_ID = Tests.Test_ID)
ON Grooves.Tire_ID = Tires.Tire_ID)
ON Points.Groove_ID = Grooves.Groove_ID
WHERE Tests.TestNumber = 'ABC123'

 

I have also tried something like this:

 

DELETE FROM Points,Grooves,Tires,Tests
WHERE Points.Point_ID IN
(SELECT * FROM Points INNER JOIN
(Grooves INNER JOIN
(Tires INNER JOIN Tests
ON Tires.Test_ID = Tests.Test_ID)
ON Grooves.Tire_ID = Tires.Tire_ID)
ON Points.Groove_ID = Grooves.Groove_ID
WHERE Tests.TestNumber = 'ABC123'

 

Neither of those work. Can anybody point me in the right direction? Thanks

[Joe Mamma]

you dont want to do this in your code.

 

the software developer shouldnt have to worry about this.

do this via referential integrity or a trigger.

 

Leverage the database. its built to do this for you.

hence the R in rdbms

 

what database?

[mjb3030]

OK. That's what I needed to know. Thanks.