Deleting 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

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting from multiple tables

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-09 : 08:56:57

I want to perform a cascade delete on multiple tables. The child tables may or may not have data contained in them. Therefore, when trying to perform the delete, no rows are affected. How can I do the following:

DELETE FROM oms.LineItem
FROM oms.LineItem li
RIGHT JOIN oms.OrderLine ol ON ol.OrderLineID = li.OrderLineID
RIGHT JOIN oms.PartOrder po ON po.PartOrderID = ol.PartOrderID
RIGHT JOIN oms.OrderHeader oh ON oh.OrderHeaderID = po.OrderHeaderID
RIGHT JOIN oms.OrderIdentifier oi ON oh.OrderID = oi.OrderID
WHERE oi.OrderID = 136

OrderIdentifier has many OrderHeaders
OrderHeaders has many PartOrders
PartOrders has many OrderLines
OrderLines has many LineItems

Thankyou

Hearty head pats

fcoreyesv
Starting Member

18 Posts

Posted - 2006-06-09 : 09:55:19

I am not an expert on SQL whatsoever but one option is to create a relationship between the tables with the option "Cascade Delete Related Records". This functionality is available in SQL 2000 and Above.

I hope it helps,

Francois

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-09 : 09:58:34

Read about Cascading Referential Integrity Constraints in sql server help file

Madhivanan

Failing to plan is Planning to fail

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-09 : 10:54:14

Hello

Thankyou for your replies.

I am aware about the cascading delete configuration option. However, we purposely do not have this enabled due to the fact it can be dangerous. Therefore, I need a method to manually perform the cascade deletes.

I have tried different methods, and have a procedure that performs the work. But I was hoping that it could be done in a single statement (like the previous posted example). That statement would work, but only if all the child tables had entries.

This is what I have so far:


CREATE PROCEDURE [oms].[Order_DeleteOrder] 
(
  @OrderID int = NULL
, @OriginatorID int = NULL
)
AS
SET NOCOUNT ON


DECLARE @IDs TABLE
(
  OrderLineID int
, PartOrderID int
, OrderHeaderID int
, OrderID int
)
INSERT INTO @IDs (OrderLineID, PartOrderID, OrderHeaderID, OrderID)
SELECT ol.OrderLineID, po.PartOrderID, oh.OrderHeaderID, oi.OrderID 
FROM oms.LineItem li
RIGHT JOIN oms.OrderLine ol ON ol.OrderLineID = li.OrderLineID
RIGHT JOIN oms.PartOrder po ON po.PartOrderID = ol.PartOrderID
RIGHT JOIN oms.OrderHeader oh ON oh.OrderHeaderID = po.OrderHeaderID
RIGHT JOIN oms.OrderIdentifier oi ON oh.OrderID = oi.OrderID
WHERE oi.OrderID = @OrderID OR oi.OriginatorPartyID = @OriginatorID

DELETE FROM oms.LineItem
WHERE OrderLineID IN (SELECT OrderLineID FROM @IDs)
DELETE FROM oms.OrderLine
WHERE OrderLineID IN (SELECT OrderLineID FROM @IDs)
DELETE FROM oms.PartOrder
WHERE PartOrderID IN (SELECT PartOrderID FROM @IDs)
DELETE FROM oms.OrderHeader
WHERE OrderHeaderID IN (SELECT OrderHeaderID FROM @IDs)
DELETE FROM oms.OrderIdentifier
WHERE OrderID IN (SELECT OrderID FROM @IDs)

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 06:35:19

It is not possible to delete data from more than one table using JOIN. you can use seperate delete statements(that you used) or to write trigger on parent table to delete child data whenever the parent data are deleted. But it is better to use what you have used now

Madhivanan

Failing to plan is Planning to fail

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-12 : 02:36:26

I don't think you want to delete the parent unless all the dependent child has been deleted. From your post:

"OrderIdentifier has many OrderHeaders
OrderHeaders has many PartOrders
PartOrders has many OrderLines
OrderLines has many LineItems"

the OrderIdentifier is the root. You want to delete the OrderIdentifier because of a single line item? I don't think this is what you want to do.

But going to deleting multiple tables in once statement, its not allowed in SQL syntax. You can use the cascade mentioned above to do that or you have to traverse like what you did, only the opposite.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources