Advanced Update query

zagnutts
zagnutts used Ask the Experts™
on
I have 2 tables:

Table 1
OrderID
ProductID
ItemQuantity
ItemName
ItemCost


Table 2
SessionID
ProductID
ItemQuantity

What I am trying to do is update Table1.ItemQuantity with values of Table2.ItemQuantity where their productIDs are equal.


Thanks for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Update Table1
Set ItemQuantity = Table2.ItemQuantity
From Table1
         Inner Join Table2 On Table1.ProductID = Table2.ProductID

Also, please maintain this old open question:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20725832.html

Thanks,
Anthony
What  is the PK for Table1, Table2?

In the scenario illustrated below, should the ItemQuantity be updated with value 5 or 6?

Table1
Product1D ItemQuantity
===============
1             4        
2             ?

Table2
Product1D ItemQuantity
===============
1             4        
2             5
2             6

SQL you are asking for is listed below. But the update may be incorrect?

Update Table1
Set      ItemQuatity = Table2.ItemQuantity
FROM Table2
WHERE Table1.ProductId = Table2.ProductId

HTH

Namasi Navaretnam




Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial