Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL query to avoid a certain rule

Author  Topic 

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-06-16 : 14:59:45
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


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

I know 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-16 : 15:10:42
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
UNION ALL
SELECT 9, ... UNION ALL
SELECT 9, ...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-06-16 : 15:16:44
When you say

UNION ALL
SELECT 9, ... UNION ALL
SELECT 9, ...

Do you mean to copy the first set so that it reads as


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
UNION ALL
SELECT 9 AS CustomerLevel UNION ALL

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-16 : 15:34:03
All I added was two extra rows to your query. I wasn't sure what you wanted for CustomerLeveID, which is why I put "...".

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -