Need to delete pairs of records 2

[briangriffin]

I have a recordset in a temp table that contains records like these (in addition to many other records that I want to keep):

Account   EffectiveDate     SequenceID    UndoneDateTime    UndoneSequenceID
9999      1/1/2016 13:00       4                   
9999      1/1/2016 13:00       8              1/2/2016 00:00            4

I need to delete both these records - anything with a value in the UndoneDateTime field, plus the corresponding original record based on the effective date and the sequence ID. I tried something like this, figured it wouldn't work but it illustrates the idea:

delete from #OPIB a inner join #OPIB b
	on a.VisitID = b.VisitID
	and a.EffectiveDate = b.EffectiveDate
	and a.UndoDateTime is not null
	and a.UndoneSeqID = b.EventSeqID

What would be the best way to delete these pairs? TIA.

 

[bborissov]

DECLARE @Test TABLE (Account char(4),  EffectiveDate datetime, SequenceID int, UndoneDateTime datetime NULL, UndoneSequenceID int NULL)

INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 4, NULL, NULL)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 8, '1/2/2016 00:00', 4)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 9, NULL, NULL)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 10, NULL, NULL)



;WITH CTE_Delete
AS
( 
 SELECT * FROM @Test YourTable WHERE UndoneDateTime IS NOT NULL
 UNION ALL
 SELECT YourTable.* 
     FROM @Test YourTable 
 INNER JOIN CTE_Delete ON  CTE_Delete.UndoneSequenceID = YourTable.SequenceID
)

DELETE YourTable
FROM @Test YourTable
INNER JOIN  CTE_Delete ON YourTable.SequenceID = CTE_Delete.SequenceID

SELECT * FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server

 

[briangriffin]

Thank you, that works perfectly but only up to about 100 records.

After that I get message 530, the maximum recursion 100 has been exhausted before statement completion.

 

[bborissov]

No, the recursive part of the CTE is exceeded 100 recursions.
Maybe you should add all other fields in the JOIN?

DECLARE @Test TABLE (Account char(4),  EffectiveDate datetime, SequenceID int, UndoneDateTime datetime NULL, UndoneSequenceID int NULL)

INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 4, NULL, NULL)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 8, '1/2/2016 00:00', 4)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 9, NULL, NULL)
INSERT INTO @Test VALUES ('9999','1/1/2016 13:00', 10, NULL, NULL)



;WITH CTE_Delete
AS
( 
 SELECT * FROM @Test YourTable WHERE UndoneDateTime IS NOT NULL
 UNION ALL
 SELECT YourTable.* 
     FROM @Test YourTable 
 INNER JOIN CTE_Delete ON  CTE_Delete.UndoneSequenceID = YourTable.SequenceID 
                       AND CTE_Delete.VisitID          = YourTable.VisitID
                       AND CTE_Delete.EffectiveDate    = YourTable.EffectiveDate
)

DELETE YourTable
FROM @Test YourTable
INNER JOIN  CTE_Delete ON YourTable.SequenceID = CTE_Delete.SequenceID

SELECT * FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server

 

[DaveInIowa]

How about a simple 2-pass delete; first deleting rows whose sequence id is one of the undone sequence ids, next deleting all rows having a non-null undone sequence id?

DELETE FROM #OPIB WHERE SequenceID IN (SELECT UndoneSequenceID FROM #OPIB)
DELETE FROM #OPIB WHERE UndoneSequenceID IS NOT NULL

 

[briangriffin]

I would also think that those additional joins would be needed, but still exceeding recursion limit.

If I select 700 records into the temp table and set max recursion to 100 it works. 800 records gets the error.

It doesn't seem to scale - 950 records fails with max of 30,000.

There will only be at most three or four records with the VisitID/EffectiveDate combination, so I'm not understanding the exponential nature of this.

 

[briangriffin]

I didn't make it clear that the sequence ID's are only unique within a VisitID/EffectiveDate combination, but a hybrid of both your suggestions seems to return correct data:

;WITH CTE_Delete

as 

(select 
a.* from #OPIB a inner join #OPIB b
	on a.VisitID = b.VisitID
	and a.EffectiveDateTime = b.EffectiveDateTime
	and a.EventSeqID = b.UndoneSeqID
	
union all
	
select * from #OPIB where UndoneDateTime is not null)


delete Results from #OPIB Results inner join CTE_Delete 
	on Results.VisitID = CTE_Delete.VisitID
	and Results.EffectiveDateTime = CTE_Delete.EffectiveDateTime
	and Results.EventSeqID = CTE_Delete.EventSeqID

select * from #OPIB order by VisitID, EffectiveDateTime, EventSeqID

I learned something new so it's a good day - thanks again.