SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Delete self referencing records (no cursors)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itai
Starting Member

4 Posts

Posted - 03/07/2011 :  05:21:58  Show Profile  Reply with Quote
Hi,
I have a table with self referencing records with a foreign key:
CREATE TABLE [dbo].[WorkOrders]
(
[WorkOrderID] [int] NOT NULL IDENTITY(1, 1),
[SalesOrderDetailsID] [int] NOT NULL,
[BillOfWorkID] [int] NOT NULL ,
[ResourceID] [int] NOT NULL ,
[WorkOrderStatusID] [smallint] NOT NULL ,
[CreatedByWorkOrderID] [int] NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrders] ADD CONSTRAINT [PK_WorkOrders] PRIMARY KEY CLUSTERED  ([WorkOrderID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrders] ADD CONSTRAINT [FK_WorkOrders_WorkOrders] FOREIGN KEY ([CreatedByWorkOrderID]) REFERENCES [dbo].[WorkOrders] ([WorkOrderID])
GO

Each work order created, has a reference work order listed in the CreatedByWorkOrderID field.
I have a dummy work order (work order id = -1) that is the creator of all "first" work orders (per sales order details ID).

The problem:
How do I delete records?

  • I have a foreign key so I can't just delete - I need to delete with a particular order.

  • I can't use "on delete cascade" because it will delete the dummy work order.

  • I don't want to use cursors due to their problems.

  • There are a few millions records in this table.

  • I tried deleting based on finding the max work order id per sales order details, delete it and do it again using a while loop.
    It works, but it took 6 hours, and I need to run it daily ...




Any ideas?

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 03/07/2011 :  07:52:24  Show Profile  Reply with Quote
You will probably have to do some looping to cope with foreign key constraint but you could make it a lot easier on yourself by creating some metadata first. Using some recursion you can find how many levels of sub-orders you have and then you can delete all the rows on a level at the same time. I think something like this should work:


ALTER TABLE workorders add OrgNode hierarchyid, level int

WITH paths(WorkOrderID, level) 
AS (
	-- This section provides the value for the root of the hierarchy
	SELECT WorkOrderID, level = 0
	FROM WorkOrders AS C 
	WHERE CreatedByWorkOrderID = -1
		AND WorkOrderID > -1
	UNION ALL 
	-- This section provides values for all nodes except the root
	SELECT C.WorkOrderID, level = p.level + 1
	FROM WorkOrders AS C 
		JOIN paths AS p 
			ON C.CreatedByWorkOrderID = P.WorkOrderID
)
UPDATE w SET level = p.level
FROM paths p
	INNER JOIN WorkOrders w
		on p.WorkOrderID = w.WorkOrderID

SELECT * from WorkOrders

DECLARE @MaxLevel int = (SELECT MAX(Level) FROM WorkOrders)

WHILE (@MaxLevel > 0)
	BEGIN
		DELETE FROM WorkOrders WHERE Level = @MaxLevel AND ---> Some more delete criteria here
		
		SET @MaxLevel = @MaxLevel - 1
	END


- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Edited by - Lumbago on 03/07/2011 07:57:03
Go to Top of Page

itai
Starting Member

4 Posts

Posted - 03/07/2011 :  10:52:44  Show Profile  Reply with Quote
Thanks Lumbago.

I'll give it a try and report back.

Itai
Go to Top of Page

itai
Starting Member

4 Posts

Posted - 03/07/2011 :  11:01:43  Show Profile  Reply with Quote
I tried the proposed solution but it failed.
Apparently, the tree level is very high - I got the following error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The high level is created because when a work order fails, it waits for a while and then creates another one.
In cases of temp error in communication (for example) I get many work orders reporting failures and creating their successors.

Now I add another condition : Can I do it without recursion / CTE ?

Itai
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 03/08/2011 :  03:32:59  Show Profile  Reply with Quote
It's much more likely that you're creating an endless recursion loop. Can you try this and see if it makes a difference? ->

WITH paths(WorkOrderID, level) 
AS (
	-- This section provides the value for the root of the hierarchy
	SELECT WorkOrderID, level = 0
	FROM WorkOrders AS C 
	WHERE CreatedByWorkOrderID = -1
		AND WorkOrderID <> CreatedByWorkOrderID
        ...

Another thing: it's much more common to set the top parent (CreatedByWorkOrderID) to NULL instead of -1...then you will not have to create a dummy work order and you will not have to filter the dummy away all the time.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 03/08/2011 :  04:12:50  Show Profile  Reply with Quote
What I would do is script Create Constraint,Drop Constraint and Check Constraint statements for the required tables,put those scripts in a table,Delete the required data and then run them in proper order.

Ex
1)Run the Drop Constraint script
2)Delete the Data
3)Run the Create Constraint script
4)Run the Check Constraint script

Try running the following script



IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tblForeignKeys')
BEGIN
	CREATE TABLE tblForeignKeys (ID INT IDENTITY(1,1), FK_name VARCHAR(255), Parent_object varchar(255), 
	Reference_object varchar(255), DropConstraint VARCHAR(max), CreateConstraint VARCHAR(max), 
	CheckConstraint VARCHAR(max)
	 )
END 

declare @min int
declare @max int
declare @debug bit
declare @cmd nvarchar(4000)

INSERT INTO tblForeignKeys (FK_name, Parent_object, Reference_object, DropConstraint,
CreateConstraint, CheckConstraint)
SELECT fk.name, object_name(fc.parent_object_id), object_name(fc.referenced_object_id),
	'ALTER TABLE ['+ object_name(fc.parent_object_id) +'] DROP CONSTRAINT ['+ fk.name +']',
	'ALTER TABLE '+ object_name(fc.parent_object_id) +'  WITH NOCHECK ADD  CONSTRAINT ['+ fk.name +'] FOREIGN KEY(['+ pc.name +'])
	REFERENCES ['+ object_name(fc.referenced_object_id) +'] (['+ rc.name +'])
	NOT FOR REPLICATION',
	'ALTER TABLE ['+ object_name(fc.parent_object_id) +'] CHECK CONSTRAINT ['+fk.name+']'
FROM sys.foreign_keys fk
	INNER JOIN sys.foreign_key_columns fc on fk.object_id = fc.constraint_object_id
	INNER JOIN sys.columns pc on fc.parent_object_id = pc.object_id and fc.parent_column_id = pc.column_id
	INNER JOIN sys.columns rc on fc.referenced_object_id = rc.object_id and fc.referenced_column_id = rc.column_id
--WHERE object_name(fc.referenced_object_id) = 'users'


select * from tblForeignKeys



PBUH

Go to Top of Page

itai
Starting Member

4 Posts

Posted - 03/09/2011 :  02:17:58  Show Profile  Reply with Quote
Sachin.Nand,
Your idea crossed my mind a few weeks ago.
For sure it will have the best performance.
The problem is that I fear for the data integrity (when I drop the constraint).
I thought about doing that with locking the transaction, but I do not want locking because the table is called (inserts and selects) very often.

If I find no other option, I might do that.

Lumbago,
I tried your solution, but I got the same error.
I don't think it is an endless recursion loop.
I know for some sales orders, I have a few thousands of work orders failing and creating another (until someone stopped it...)
This will cause a tree of a few thousands level deep - right? Anyway, more than 100 levels permitted (if I get it right),

Itai
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 03/09/2011 :  03:57:05  Show Profile  Reply with Quote
I still believe that going down this "level-path" is the right way to go but getting there is seemingly quite tedious. If you do have this level correctly stored in your table though I think it will be far easier to do maintenance, like deleting. I found a (childishly simple) way to create the levels but it involves some pretty heavy looping on the table so I'd suggest you only do this once (if you want to do it at all) and instead try to create the correct level when you create a new work order, based on the level of the CreatedByWorkOrder.

This is quite nasty but it will loop once for every level and you can stop it and continue whenever you want:
SET NOCOUNT ON
GO
UPDATE WorkOrders SET Level = 1 WHERE CreatedByWorkOrderID = -1
GO
WHILE EXISTS (SELECT 1 FROM WorkOrders WHERE Level IS NULL)
	BEGIN
		UPDATE child SET Level = parent.Level + 1
		FROM WorkOrders child
			INNER JOIN WorkOrders parent
				ON child.CreatedByWorkOrderID = parent.WorkOrderID
		WHERE parent.Level IS NOT NULL
			AND child.Level IS NULL
		
		--> If you want to give the process a breather between each iteration
		--> and to open up for other processes to do some work
		--> WAITFOR DELAY '00:00:05'
	END
GO


- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

jayrocks20
Starting Member

1 Posts

Posted - 07/23/2014 :  05:30:26  Show Profile  Reply with Quote
I have similar table and similar requirement .it ends in infinite loop ,runs for hours and nothing gets deleted. I have to kills it,.
any other way to do this other than dropping constraints. with CTE etc?


Thanks,
J
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000