CODEWITHSUNDEEP
sqlwindow-functionssql-deletepartition-by
RebeccaJohnson
RebeccaJohnson

Reputation: 43

Window Function with multiple conditions to DELETE records from Table

This is my table:

I need to delete those records where there is NL46 ShopCode, but only if the ProductCode has ShopCode 'FR43','FR44' or 'FR45'. I only need to keep the rows with NL46 if these ShopCodes don't belong to the ProductCode.

I used WindowFunction:

DELETE FROM MyTable
WHERE ShopCode = 'NL46' AND SUM(CASE WHEN SHOPCODE = 'NL46' THEN 0 ELSE 1) OVER  PARTITION BY ProductCode > 0

But it does not work.

The confusing thing is that there are other ShopCodes which I don't know how to handle.

Upvotes: 0

Views: 99

Answers (2)

Jonas Metzler
Jonas Metzler

Reputation: 5975

We can use two IN clauses, one checking the product code and one checking the shop code:

DELETE FROM MyTable
WHERE 
  ShopCode = 'NL46'
  AND ProductCode IN
    (SELECT ProductCode FROM MyTable WHERE ShopCode IN ('FR43', 'FR44', 'FR45'));

See this demo with your sample data.

Upvotes: 0

jarlh
jarlh

Reputation: 44795

I'd simply use EXISTS to make sure at least one of 'FR43', 'FR44' and 'FR45' also exists.

DELETE FROM MyTable m1
WHERE ShopCode = 'NL46'
  AND EXISTS (SELECT * FROM MyTable m2
              WHERE m2.ProductCode = m1.ProductCode 
                AND m2.ShopCode IN ('FR43', 'FR44', 'FR45'))

Upvotes: 2

Related Questions