Multiple Parameter Update - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Parameter Update

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2005-02-14 : 00:34:12

Is there a way to use several parameters from one query to update multiple records in another table? For example, the query obtains the ItemNumber and ItemPrice from Table1 for a variety of items:

ItemNumber | NumSold
1 | 25
2 | 35
3 | 40

The query is "SELECT ItemNumber, NumSold FROM Table1 T1 ORDER BY ItemNumber".

Is there a way to incorporate this query into an UPDATE statement which modifies records in another table corresponding with each ItemNumber and quantity sold? For example,

UPDATE Table2 T2 SET Inventory = Inventory - T1.NumSold, Revenue = Revenue + (T1.NumSold * Price) WHERE T2.ItemNumber = T1.ItemNumber

Any assistance on this would be greatly appreciated. Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-14 : 01:15:11

UPDATE Table2 T1
SET Inventory = t1.Inventory - T2.NumSold, Revenue = t1.Revenue + (t2.NumSold * t1.Price)
from Table2 t1
join Table1 t2
on t1.ItemNumber = t2.ItemNumber

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources