How to Delete Multiple Rows With Multiple SELECT Statements In WHERE Clause in DB2

Question

In the given inventory table below:

SKU	COST	DATE YYMMDD
202114000	0	210109
202114000	12	210228
202115000	50	210108
202115000	30	210109
202116000	54	210103
202116000	60	210301
202116000	100	210605

I want to retain SKU with the latest date and remove all others, and just retaining one SKU per row.

My query script goes:

SELECT *
FROM `inventory`
WHERE
    INUMBR IN (
        SELECT 
            SKU
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
    AND
    DATE IN (
        SELECT
            MIN (DATE) as DATE
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
ORDER BY
    SKU

However, this script is problematic. Why? because it will delete the two rows with the SKU 20211500 because, on the second IN criteria in the WHERE clause, two of DATEs will match: 210109 and 210108. How can I improve my query?

NOTE: The table is just an example, I will be dealing with millions of rows here.

Answer 1

You can delete from a derived table as long as there is a bijection to the underlaying base table. Here I enumerate the rows per sku in reversed order and delete all but the first one:

delete from ( 
    select sku, cost, dt
         , row_number() over (partition by sku 
                              order by dt desc) as rn 
    from inventory
) 
where rn > 1


select * from inventory

SKU                COST        DT          
------------------ ----------- ------------
202114000                   12 210228      
202115000                   30 210109      
202116000                  100 210605

Note, I used dt as a column name since date is a reserved word. I also assumed that sku, dt is the primary key of the table, if not the result will be non-deterministic.

Answer 2

Well, I got it. I will just use MAX, instead of MIN. And I just also learned that you can give two parameters in the WHERE clause.

DELETE
FROM `inventory`
WHERE (SKU, DATE)
    NOT IN (
        SELECT
           SKU,
           MAX(DATE) as DATE
        FROM `inventory`
        WHERE
           SKU IN (
              SELECT
                 SKU
              FROM
                 `inventory`
              GROUP BY
                 SKU
              HAVING
                 COUNT(*) > 1
              ORDER BY
                 SKU
           )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
    AND
    SKU NOT IN (
            SELECT
                SKU
            FROM
                `inventory`
            GROUP BY
                SKU
            HAVING
                COUNT(*) = 1
            ORDER BY
                SKU
        )
    AND 
    SKU!= 0

Let me know if there is still a way to further improve this query.