How to remove duplicate rows with CTE when partitioning by another table's column?

Question

I'm trying to adopt this solution to remove duplicate rows from a database table. However, in my case, whether two rows are considered "duplicates", another table must be checked. A full repro for my scenario would be like this:

-- Foreign key between these tables as well as "Group" table omitted for simplicity...
DECLARE @ItemType TABLE(Id INT, Title NVARCHAR(50), GroupId INT);
DECLARE @Item TABLE(Id INT IDENTITY(1,1), ItemTypeId INT, Created DATETIME2);

INSERT INTO @ItemType (Id, Title, GroupId) 
VALUES  (1, 'apple', 1), (2, 'banana', 1), (3, 'beans', 2);

INSERT INTO @Item (ItemTypeId, Created) 
VALUES  (1, '20141201'), (2, '20140615'), (3, '20140614');
-- Note: Id's are generated automatically

WITH cte AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Created) AS Rnk
    FROM   @Item AS i
           JOIN @ItemType AS it ON i.ItemTypeId = it.Id
)
DELETE FROM cte 
WHERE Rnk > 1;

This fails, obviously, with the following message:

View or function 'cte' is not updatable because the modification affects multiple base tables.

Can this be solved while sticking with the elegant cte-solution? Or does this require a move over to a version based on DELETE or even MERGE INTO?

Rows in `@Item` should be deleted. The `JOIN` is merely meant to find out in which "Group" items fall, as I only want to "de-duplicate" within each group. — Jeroen, Dec 24 '14 at 13:05

score 2 · Accepted Answer · answered Dec 24 '14 at 12:53

2

You can stick with the CTE version, but the DELETE has to be more explicit about which rows it's going to remove. Just pass the @Item.Id from the CTE to and filter to be deleted rows based on that:

WITH cte AS (
    SELECT i.Id,
           ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Created) AS Rnk
    FROM   @Item AS i
           JOIN @ItemType AS it ON i.ItemTypeId = it.Id
)
DELETE FROM @Item
WHERE Id IN (SELECT Id FROM cte WHERE Rnk > 1);

answered Dec 24 '14 at 12:53

Jeroen

60,696
40
206
339

The problem is much easier if you have an `id` in the `@Item` table, something not mentioned in the question. – Gordon Linoff Dec 24 '14 at 13:26
@GordonLinoff Ahh yes, I do very much apologize, this may not've been clear from the question (it's kind of "hidden" as it's an `IDENTITY` column). My question started out as a real question, and as I wrote it I thought of a solution. Instead of throwing my text away I figured I might as well share as a self-answered question. I will update the question to make it clear there's an `Id` column that can be used. Apologies if by this I wasted some of your time, I had the best intentions! – Jeroen Dec 24 '14 at 13:36

How to remove duplicate rows with CTE when partitioning by another table's column?

1 Answers1