INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need to delete pairs of records

Need to delete pairs of records

(OP)
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.

RE: Need to delete pairs of records

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

RE: Need to delete pairs of records

(OP)
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.

RE: Need to delete pairs of records

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

RE: Need to delete pairs of records

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 

RE: Need to delete pairs of records

(OP)
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.





RE: Need to delete pairs of records

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close