Over a million developers have joined DZone.

SQL Server: Deleting with Self-Referential FOREIGN KEY, Handling Loops

· Java Zone

Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code! Brought to you in partnership with ZeroTurnaround.

From comments to one of my previous posts: SQL Server: deleting with self-referential FOREIGN KEY

In my case the query goes to infinite loop.

If I use OPTION (MAXRECURSION 365) or OPTION (MAXRECURSION 3267), it fails when reaching highest recursion value.

If I use 0 (which is infinite) the query runs for an hour with no output.

I understand it is a data related issue which is causing infinite loop, but I cannot control the data being sent to the DB.

Any other way we can handle it?

Thanks,
Jay

That's a common situation, and we would sometimes need to clean the mess up (i. e. remove the whole loop).

To do this, we would just need to add a little check condition to the recursive CTE.

We just record the topmost entry (or better say the entry we first use in the loop, as there is obviously no topmost entry), and check if we ever come across it again as we do the recursion.

Once we have it on next iteration, we know it's a loop and it's time to stop.

Here's how we do this:

CREATE TABLE TestComposite (
        id INT NOT NULL, 
        siteUrl NVARCHAR(255) NOT NULL,
        name NVARCHAR(MAX) NOT NULL,
        parentId INT NULL,
        PRIMARY KEY (id, siteUrl),
        FOREIGN KEY (parentId, siteUrl) REFERENCES TestComposite (id, siteUrl)
)
GO

INSERT
INTO    TestComposite
VALUES  (1,  'site1', 'name1', NULL),
        (2,  'site2', 'name2', 4),
        (3,  'site2', 'name3', 2),
        (4,  'site2', 'name4', 3),
        (5,  'site5', 'name5', NULL),
        (6,  'site5', 'name6', 5)
GO

WITH    q (start, id, siteUrl) AS
        (
        SELECT  id, id, siteUrl
        FROM    TestComposite
        WHERE   id = 2
                AND siteUrl = 'site2'
        UNION ALL
        SELECT  start, tc.id, tc.siteUrl
        FROM    q
        JOIN    TestComposite tc
        ON      tc.parentID = q.id
                AND tc.siteUrl = q.siteUrl
        WHERE   tc.id <> q.start
        )
DELETE
FROM    TestComposite
OUTPUT  DELETED.*
WHERE   EXISTS
        (
        SELECT  id, siteUrl
        INTERSECT
        SELECT  id, siteUrl
        FROM    q
        )

We had a loop on 2 -> 3 -> 4 which is gone after the query.

See this query on SQL Fiddle.


The Java Zone is brought to you in partnership with ZeroTurnaround. Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code!

Topics:

Published at DZone with permission of Alex Bolenok, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}