Delete from multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete from multiple tables

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-06-09 : 14:03:12

OK, this is a big problem, with multiple tables, but here goes. Here's my schema:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------

--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------

--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------

--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID
--------------------------------------------------

--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID
--------------------------------------------------

--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID
--------------------------------------------------

OK, what I'm trying to do is, when I delete an event, I need to delete all the data associated with that event. So here's the thought process.

Delete Event based on ID
Delete all EventRegistration where ER_EventID = Event.ID
Delete all RegistrantOptions where RO_EventRegistrationID = EventRegistration.ID
Delete all EventOptionGroups where EOG_EventID = Event.ID
Delete all OptionGroups where OptionGroups.ID = EOG_OptionGroupID
Delete all Options where O_OptionGroupID = OptionGroups.ID

Sorry that it's so complicated, by I need help. There are foreign key constraints on the tables as well, so you have to work from the bottom back up.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 14:13:35

DECLARE @id int

SET @id = 1234

DELETE ct1
FROM ChildTable1 ct1
INNER JOIN ParentTable pt
ON ct1.SomeColumn = pt.SomeColumn
WHERE pt.A = @id

DELETE ct2
FROM ChildTable2 ct2
INNER JOIN ParentTable pt
ON ct2.SomeColumn = pt.SomeColumn
WHERE pt.A = @id

DELETE ct3
FROM ChildTable3 ct3
INNER JOIN ChildTable4 ct4
ON ct3.SomeColumn = ct4.SomeColumn
INNER JOIN ParentTable pt
ON ct4.SomeColumn = pt.SomeColumn
WHERE pt.A = @id

DELETE ct4
FROM ChildTable4 ct4
INNER JOIN ParentTable pt
ON ct4.SomeColumn = pt.SomeColumn
WHERE pt.A = @id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 14:17:28

Or If you have control over schema, you could simply modify all foreign key definition with ON DELETE CASCADE feature. When you specify this while creating a foreign key, any record deletion in the parent table will automatically remove the related records in the child tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources