Update a column if data not found in new table

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

Update a column if data not found in new table

Author

Topic

truthseeker
Starting Member

15 Posts

Posted - 2010-09-30 : 13:12:20

Hi gurus!

I hope you can help me with this. I have two tables: Products and NewProducts.

Products has 3 columns: sku, brand, active
NewProducts has 2 columns: sku, brand

The Products table has many brands, the NewProducts table has only 1 particular brand.

What I need to do is if a certain product in the Products table does not appear in the NewProducts table, then Products.Active would be set to 0 with the following conditions:

1. The Products.SKU does not appear in the NewProducts table.
2. Only those Products with the same brand in the NewProducts table will be affected.

I tried the following query but all the records are being affected.

UPDATE Products
SET active = 0
FROM Product, NewProduct
WHERE Product.SKU NOT IN
(SELECT NewProduct.SKU FROM Product, NewProduct
WHERE NewProduct.Brand = Product.Brand)

Thanks for your inputs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 13:15:51

[code]
UPDATE Products
SET active = 0
WHERE NOT EXISTS
(SELECT 1 FROM NewProduct
WHERE NewProduct.Brand = Product.Brand
AND NewProduct.Sku= Product.Sku)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

truthseeker
Starting Member

15 Posts

Posted - 2010-09-30 : 13:48:04

quote:
Originally posted by visakh16

UPDATE Products
SET active = 0
WHERE NOT EXISTS
(SELECT 1 FROM NewProduct
WHERE NewProduct.Brand = Product.Brand
AND NewProduct.Sku= Product.Sku)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, Visakh16. I tried this but with the same result.

Let's say Products table have these rows:

SKU Brand Active
a x 1
b y 1
c z 1
d y 1

NewProducts has this:

SKU Brand
e y

When I run my and your query the results return

SKU Brand Active
a x 0
b y 0
c z 0
d y 0

Which should not be. The Active field must still be 1 because though the 2nd and 4th row in the Products table do have the same Brand as that one in the NewProduct table; nevertheless, they have no matching SKUs. The SKU and the Brand in the Products table MUST match with the SKU and Brand in the other table.

Now let's say the NewProduct table has this value:

SKU Brand
b y

Then the result of the update should be:

SKU Brand Active
a x 0
b y 1
c z 0
d y 0

I hope I'm stating it clearly

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-09-30 : 15:11:17

You are contradicting yourself.

If this comment states it clearly "The SKU and the Brand in the Products table MUST match with the SKU and Brand in the other table.", then Visakh's code works as is with your original data set. The brand (y) matches but there isn't a correpsonding SKU (e) so it sets the records to inactive. The second dataset would work with his code as well, provided the active flag was already set to "1" on the product table.

If this comment states it clearly, "The Active field must still be 1 because though the 2nd and 4th row in the Products table do have the same Brand as that one in the NewProduct table; nevertheless, they have no matching SKUs", simply remove 'AND NewProduct.Sku= Product.Sku' from Visakh's code.

Terry

-- You can't be late until you show up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources