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.
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 8DELETE FROM ProductCustomerLevel WHERE CustomerLevelID IN (0, 5, 7, 8)INSERT ProductCustomerLevel (ProductID,CustomerLevelID)SELECT ProductID,CustomerLevelFROM dbo.Product pCROSS JOIN (SELECT 0 AS CustomerLevel UNION ALLSELECT 5 UNION ALLSELECT 7 UNION ALLSELECT 8)cBasically 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,CustomerLevelFROM dbo.Product pCROSS JOIN (SELECT 0 AS CustomerLevel UNION ALLSELECT 5 UNION ALLSELECT 7 UNION ALLSELECT 8)cUNION ALLSELECT 9, ... UNION ALLSELECT 9, ...Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2014-06-16 : 15:16:44
|
When you sayUNION ALLSELECT 9, ... UNION ALLSELECT 9, ...Do you mean to copy the first set so that it reads as INSERT ProductCustomerLevel(ProductID,CustomerLevelID)SELECT ProductID,CustomerLevelFROM dbo.Product pCROSS JOIN (SELECT 0 AS CustomerLevel UNION ALLSELECT 5 UNION ALLSELECT 7 UNION ALLSELECT 8)cUNION ALLSELECT 9 AS CustomerLevel UNION ALL |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|