SQL script to not allow insert

Question

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

I read it wrong so I deleted my answer. Apologies if I sent you down the wrong road and wasted time! — PopeDarren
– PopeDarren, Commented Jun 16, 2014 at 20:10

Joachim Isaksson · Accepted Answer · 2014-06-16 19:56:26Z

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.

great thanks! Like the SQLfiddle also! Never knew about that site

Collectives™ on Stack Overflow

SQL script to not allow insert

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related