deleting list of groups in sql

Question

In my query

SELECT  
    [Dt],
    [ItemRelation],
    [DocumentNum],
    [DocumentDate],
    [CalendarYear]
FROM
    [Action].[dbo].[testtable]

These columns are grouping:

  [ItemRelation]
  [DocumentNum]
  [CalendarYear]

Any groups I must delete.

So here data for query

DECLARE @LIST_ABOVE TABLE (ItemRelation NVARCHAR(10),
                           DocumentNum NVARCHAR(10),
                           CalendarYear INT)

INSERT INTO @LIST_ABOVE (ItemRelation, DocumentNum, CalendarYear)
VALUES
    (11511,5,2017),
    (11628,2,2017),
    (11661,163,2017),
    (11692,82,2017),
    (11709,143,2017),
    (13189,33,2017),
    (13284,2,2017),
    (158009,12,2017),
    (158121,63,2017),
    (11514,60,2017),
    (11628,3,2017),
    (11671,13,2017),
    (11706,8,2017),
    (11741,163,2017),
    (13191,7,2017),
    (13284,3,2017),
    (158010,12,2017),
    (158122,41,2017),
    (11592,33,2017),
    (11628,140,2017),
    (11683,70,2017),
    (11706,50,2017),
    (13163,70,2017),
    (13191,33,2017),
    (13322,4,2017),
    (158010,89,2017),
    (158122,62,2017),
    (11594,9,2017),
    (11633,75,2017),
    (11683,140,2017),
    (11706,51,2017),
    (13163,75,2017),
    (13250,83,2017),
    (13322,36,2017),
    (158010,95,2017),
    (158122,63,2017),
    (11623,71,2017),
    (11634,154,2017),
    (11683,154,2017),
    (11706,58,2017),
    (13163,131,2017),
    (13269,50,2017),
    (157186,57,2017),
    (158121,41,2017),
    (11626,29,2017),
    (11661,143,2017),
    (11683,163,2017),
    (11709,81,2017),
    (13189,13,2017),
    (13269,66,2017),
    (157192,56,2017),
    (158121,62,2017)

When I run this script

DELETE T FROM [Action].[dbo].testtable T
WHERE EXISTS (SELECT 1 
              FROM @LIST_ABOVE 
              WHERE T.[ItemRelation] = [ItemRelation] 
                AND T.[DocumentNum] = [DocumentNum] 
                AND T.[CalendarYear] = [CalendarYear]);

from table, the rows with indicated above groups weren't deleted. every group has 40 rows.

so 40*52=2080 rows must be deleted.

How to delete rows with this groups?

I just don't want do it manually. But I can not delete them.

Answer 1

For a start, you've declared ItemRelation and DocumentNum as NVARCHAR(10) but inserted them as integers. In itself that's not an issue but if the data types in testtable are numeric, then a datatype mismatch could explain it.

DELETE T 
FROM [Action].[dbo].testtable AS T
INNER JOIN @LIST_ABOVE AS LA
ON LA.ItemRelation = T.ItemRelation
AND LA.DocumentNum = T.DocumentNum
AND LA.CalendarYear = T.CalendarYear

If that doesn't work, then I'd suggest trying to delete the first item in @LIST_ABOVE

DELETE FROM [Action].[dbo].testtable AS T
WHERE T.ItemRelation = 11511
AND T.DocumentNum = 5
AND T.CalendarYear = 2017

In the end you need to determine why no deletes occur. Data type mismatches seem most likely, data just not being there in [Action].[dbo].testtable is another possibility.