Updating inventory after inner join

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

Updating inventory after inner join

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-30 : 11:08:38

I have this script which pulls in all products from a product inventory with a sku range starting in 9 and ending in BK

select Product.ProductID,Product.Name,Product.SKU,ProductVariant.Inventory
From Product
Inner join ProductVariant
on Product.ProductID=ProductVariant.ProductID
where Product.SKU like '9%BK'

If I wanted to add something to this script where the inventory for all those products in the results above was changed from 0 to 20 how would I add this? Just changing those who had a a SKU starting with 9 and ending in BK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-30 : 11:20:41

quote:
where the inventory for all those products in the results above was changed from 0 to 20

where is these information ? in which column ?

KH
[spoiler]Time is always against us[/spoiler]

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-30 : 11:37:28

quote:
Originally posted by khtan

quote:
where the inventory for all those products in the results above was changed from 0 to 20

where is these information ? in which column ?

KH
[spoiler]Time is always against us[/spoiler]

The ProductVariant.Inventory column

so I would run something like

UPDATE ProductVariant
SET Inventory = '20'
WHERE Inventory = '0'

But within the same script as above so it does not update all products.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-30 : 11:47:38

[code]Update ProductVariant
Set Inventory = '20'
From Product
Inner join ProductVariant
on Product.ProductID = ProductVariant.ProductID
where Product.SKU like '9%BK'
and ProductVariant.Inventory = '0'[/code]

KH
[spoiler]Time is always against us[/spoiler]

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-30 : 12:04:18

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources