CODEWITHSUNDEEP
sql
Dean
Dean

Reputation: 159

SQL script to not allow insert

I have a query that maps all products to some customer levels. In this case levels 0,5,7 and 8

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8)

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 0 AS CustomerLevel UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 8
)c

The reason that the Delete begins the SQL script is so that any product that may have been deleted or unmapped from the sites is counted for

Basically this maps all products in a database to these customer levels so that they get a discount.

I now need to create a new customer level, example number 9. These will only have 1 or 2 products applied to it.

How can I change the SQL above so that it does not map those products already in Customer Level 9 to levels 0,5,7 and 8

Upvotes: 1

Views: 64

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can just append a WHERE clause to the end that excludes all products that have an existing connection with level 9;

WHERE ProductID NOT IN (
  SELECT ProductID FROM ProductCustomerLevel 
  WHERE CustomerLevelID=9
)

An SQLfiddle to test with.

Upvotes: 1

Related Questions