Exluding value from Insert - SQL Server Forums

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

Exluding value from Insert

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-06-17 : 06:27:42

Hi

Yesterday I got this script working. The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week.

However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is this

All products mapped to customer levels 9,10 and 14

Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.

Any ideas?

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

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 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 14
)c
WHERE ProductID NOT IN (
SELECT ProductID FROM ProductCustomerLevel
WHERE CustomerLevelID=9
OR CustomerLevelID=10
OR CustomerLevelID=14
)

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-17 : 07:31:36

If you are doing a cross join do you need the where at the bottom?
wont the ProductID be the same here, the OR at the bottom will calc those because you are saying ok then well do these IDs too?

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

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 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 14
)c
WHERE ProductID NOT IN (9,10,14)
SELECT ProductID FROM ProductCustomerLevel

We are the creators of our own reality!

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-06-17 : 08:03:10

Thanks But did not work unfortunately. I guess the closest I have is this

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
WHERE ProductID NOT IN (
SELECT ProductID FROM ProductCustomerLevel
WHERE CustomerLevelID=9
OR CustomerLevelID=10
OR CustomerLevelID=14
)

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-17 : 08:52:03

Why have you aliased dbo.Product? you have not called it?

WHERE p.ProductID NOT IN (9,10,14)
SELECT ProductID FROM ProductCustomerLevel

We are the creators of our own reality!

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-06-17 : 09:58:11

Yes I did include it, the last statement was just what was working before. It still maps every product to all categories however.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-17 : 11:02:59

Without the data its awkward but really a merge would be a better option, also case statement:

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

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT [ProductID],
[CustomerLevel,
0 AS CustomerLevel UNION ALL,
5 UNION ALL,
7 UNION ALL,
8,
[Level] = Case
When [CustomerLevelID] In (9,10,14) Then p.[ProductID]
When [CustomerLevelID] In (0,5,7,8) Then p.[ProductID]
End

FROM dbo.Product

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources