Delete rows by user ID using product hierarchy in CTE-CodePudding

I have a table with hundreds of thousands of user IDs and product codes. I use a CTE script within a WHILE loop to remove product IDs according to a hierarchy--i.e. if a user has products 18 and 19, I delete 19. If user has 17, 18, and 19, I delete 18 and 19. The problem is the loop is too costly and time consuming to work on the production tables.

The product IDs are not ordinal--they are simply IDs. My goal is to remove product ID rows according to the hierarchy shown in the code below--e.g. if 17 exists for user 1, remove products 18 and 19 if either or both exist. If product 18 and 19 exist such as for user 1, remove only 19 leaving product 18 alone.

In the following example, the lowest number in a group needs to be kept, but that is not always the case so I can't simply group product types and keep the MIN.

In the quick example below, user 1 should keep 18 and user 2 should keep 17. Instead, only user 2 is kept and both products are deleted for user 1. I can't figure out what I'm missing.

CREATE TABLE #tmp (
    ID varchar(1),
    Prod varchar(2)
)
INSERT INTO #tmp
VALUES ('1', '18')
      ,('1', '19')
      ,('2', '17')
      ,('2', '18')
      ,('2', '19')

--SELECT * FROM #tmp ORDER BY ID

;WITH CTE (ID, Prod)
        AS
        (SELECT ID, Prod FROM #tmp)
        DELETE FROM CTE
        WHERE (Prod = 19
            AND EXISTS (SELECT t.Prod
                        FROM #tmp t
                            INNER JOIN CTE ON t.ID = cte.ID 
                                AND t.Prod = CTE.Prod
                        WHERE t.Prod = 18
                        ))
        OR  (Prod = 18
            AND EXISTS (SELECT t.Prod
                        FROM #tmp t
                            INNER JOIN CTE ON  t.ID = cte.ID
                                AND t.Prod = CTE.Prod
                        WHERE t.Prod = 17   
                        ))

CodePudding user response：

Your "EXISTS" queries pretty much boil down to "are there any 18s in the table" and "are there any 17s in the table", which are both true, so all 19s and 18s will be deleted.

I think you want this

;WITH CTE (ID, Prod) AS (SELECT ID, Prod FROM #tmp)
DELETE FROM CTE
WHERE (Prod = 19 AND EXISTS (SELECT * FROM #tmp t WHERE Prod = 18 AND t.ID = CTE.ID))
OR  (Prod = 18 AND EXISTS (SELECT * FROM #tmp t WHERE Prod = 17 AND t.ID = CTE.ID))