Mass Update from Temp Table - 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 2005 Forums

Transact-SQL (2005)

Mass Update from Temp Table

Author

Topic

ajwaka
Starting Member

2 Posts

Posted - 2007-12-12 : 12:55:38

I have a situation where I've uploaded a text file to a temp table in my db. Now - my temp table is like this
[TempTable]
Item
Qty
Price

Now - I'm wanting to update my products price based on what may be given in the temp table. But here's the kicker

- I have a [Product] table and a [ProductPrice] table because each table "could" have multiple prices (third party store) - but ours don't.

So the Item from the [TempTable] is a value that is found in [Product] - but QTY and Price are updated in [ProductPrice].

here's my thinking but cannot figure out how to "loop" and use each record in the [TempTable].

update ProductPrice a
set a.Price = 20 , a.Qty = 275
from ProductPrice a inner join Product b on a.ProductID = b.ProductID
where b.Item = 'item12'

And the respective Price, Qty and Item are in the Corresponding Temp table

Your help is greatly appreciated!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-12 : 13:12:39

You can directly update fields as follows:-

update a
set a.Price = #t.Price , a.Qty = #t.Qty
from ProductPrice a
inner join Product b
on a.ProductID = b.ProductID
inner join #Temp #t
on #t.Item=b.Item

ajwaka
Starting Member

2 Posts

Posted - 2007-12-12 : 13:29:47

My goodness - I'm embarrassed for overlooking the obvious!!! Been a long day!

Thanks for the reply!!! Very Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources