Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to delete pairs of records 2

Status
Not open for further replies.

briangriffin

Programmer
Nov 6, 2008
878
US
I have a recordset in a temp table that contains records like these (in addition to many other records that I want to keep):

Code:
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:

Code:
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.
 
Code:
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
 
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.
 
No, the recursive part of the CTE is exceeded 100 recursions.
Maybe you should add all other fields in the JOIN?
Code:
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
 
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?

Code:
DELETE FROM #OPIB WHERE SequenceID IN (SELECT UndoneSequenceID FROM #OPIB)
DELETE FROM #OPIB WHERE UndoneSequenceID IS NOT NULL
 
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.





 
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:

Code:
;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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top