How delete in specific order

Question

I have a column in table which reference the same table (as a parent-child relationship).

When I want to purge like this (C# Code):

do
{
    commandRows = context.Database.ExecuteSqlCommand("DELETE top(5000) from IssuerRequests WHERE discriminator='IssuerRequest' and Issuer_ID = @Issuer_ID and ExpireDate < @LimitDate",
        new SqlParameter("@Issuer_ID", customer.ID), new SqlParameter("@LimitDate", LimitDate));
    AffectedRows += commandRows;
}
while (commandRows >= 5000);

An exception appears:

The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_dbo.IssuerRequests_dbo.IssuerRequests_LinkedRequestForMultichannel_ID". The conflict occurred in database "xxx", table "dbo.IssuerRequests", column 'LinkedRequestForMultichannel_ID'. The statement has been terminated.

This error is normal because if we try to delete a line referenced by another one, the system doesn't allow it.

So I manage a script in order to purge manually :

USE [Database]

DECLARE @DaysAfterExpiring INT
DECLARE @Issuer_ID INT
DECLARE @DateAfterExpiring DATETIME

SET @DaysAfterExpiring = 35
SET @DateAfterExpiring = DATEADD(day, -(@DaysAfterExpiring), GETUTCDATE())

SELECT @Issuer_ID = [ID] FROM IssuerDescs WHERE [DisplayName] LIKE '%toto%'

DECLARE @MoreRowsToDelete BIT
SET @MoreRowsToDelete = 1

-- With the relationship
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE TOP (1000) FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NOT NULL
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NOT NULL)
    BEGIN
        SET @MoreRowsToDelete = 1
    END
END

-- Without the relationship
SET @MoreRowsToDelete = 1
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE TOP (1000) FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NULL
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring  AND LinkedRequestForMultiChannel_ID IS NULL)
    BEGIN
        SET @MoreRowsToDelete = 1
    END
END

Do you know if it's possible to avoid double loops and delete 5000 lines per action without doing the distinction?

I thought about "ORDER BY" in delete with CTE but I'm not sure about the result.

I will try this possible solution :

USE [database]

DECLARE @DaysAfterExpiring INT
DECLARE @Issuer_ID INT
DECLARE @DateAfterExpiring DATETIME

SET @DaysAfterExpiring = 35
SET @DateAfterExpiring = DATEADD(day, -(@DaysAfterExpiring), GETUTCDATE())

SELECT @Issuer_ID = [ID] FROM IssuerDescs WHERE [DisplayName] LIKE '%toto%'

DECLARE @MoreRowsToDelete BIT
SET @MoreRowsToDelete = 1

-- With the relationship first then without the relationship
WHILE @MoreRowsToDelete = 1
BEGIN
    DELETE T FROM (SELECT TOP (1000) * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring ORDER BY ISNULL(LinkedRequestForMultiChannel_ID, '') DESC) AS T
    IF NOT EXISTS (SELECT TOP 1 * FROM IssuerRequests WHERE discriminator='IssuerRequest' AND Issuer_ID = @Issuer_ID AND [ExpireDate] < @DateAfterExpiring)
    BEGIN
        SET @MoreRowsToDelete = 0
    END
END

Answer 1

The example at the end of my original post works as attended. Because the script will delete first every lines with any FK reference, and then lines without any FK reference. I'm using batch of 1 000 lines in order to avoid to lock the table.

So the solution is to use CTE or specific query after the FROM (as comments, it's the same result) clause.

Answer 2

You can use a recursive CTE to delete all of them in one go.

As long as all child rows are deleted at the same time, it works to delete them in the same statement as the child rows. There is no need for an ORDER BY as the server can work out that the parent and child are being deleted at the same time.

WITH cte AS (
    SELECT
      ir.ID,
      ir.LinkedRequestForMultiChannel_ID
    FROM IssuerRequests ir
    JOIN IssuerDescs id ON id.ID = ir.Issuer_ID
    WHERE id.DisplayName LIKE '%toto%'
      AND ir.discriminator = 'IssuerRequest'
      AND ir.ExpireDate < @DateAfterExpiring

    UNION ALL

    SELECT
      ir.ID,
      ir.LinkedRequestForMultiChannel_ID
    FROM IssuerRequests ir
    JOIN cte ON cte.LinkedRequestForMultiChannel_ID = ir.ID  -- is this the correct join?
)
DELETE ir
FROM cte
JOIN IssuerRequests ir ON ir.ID = cte.ID;

If you want to batch them then it's more complicated, as you need to make sure all the more distant levels are deleted first.

WHILE 1=1
BEGIN

WITH cte AS (
    SELECT
      ir.ID,
      ir.LinkedRequestForMultiChannel_ID,
      1 AS Level
    FROM IssuerRequests ir
    JOIN IssuerDescs id ON id.ID = ir.Issuer_ID
    WHERE id.DisplayName LIKE '%toto%'
      AND ir.discriminator = 'IssuerRequest'
      AND ir.ExpireDate < @DateAfterExpiring

    UNION ALL

    SELECT
      ir.ID,
      ir.LinkedRequestForMultiChannel_ID
      cte.Level + 1
    FROM IssuerRequests ir
    JOIN cte ON cte.LinkedRequestForMultiChannel_ID = ir.ID  -- is this the correct join?
)
DELETE ir
FROM (
    SELECT TOP (4500) *
    FROM cte
    ORDER BY
      cte.Level DESC
) cte
JOIN IssuerRequests ir ON ir.ID = cte.ID;

IF @@ROWCOUNT = 0
    BREAK;

WAITFOR DELAY '00:00:01';

END;