Updating temp table with sum from other temptable and join

[sodakotahusker]

I have two temp tables. One is all the records. The other is a summary of table one where I combine the quanity for similar skus.


create table #tempSkus ([ID] int, ClaimID int, ProductCode varchar(50),
Quantity int, InvoiceNumber varchar(100), ClaimInvoiceID int, ProductID int)

create table #SKU ([ID] int, ClaimID int, ProductCode varchar(50),
Quantity int, InvoiceNumber varchar(100), ClaimInvoiceID int, ProductID int)


update t
set quantity = (select sum(s.quantity)
from #tempskus t
inner join #sku s
on t.productid = s.productid and t.claiminvoiceid = s.claiminvoiceid
group by s.claiminvoiceID,s.productID)

I get an error that t is an invalid object.
How do I can my data consolidated into the new database successfully ??? PS: The first table is being build from an xml string.

 

[bborissov]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #tempSkus ([ID] [COLOR=blue]int[/color], ClaimID [COLOR=blue]int[/color], ProductCode [COLOR=blue]varchar[/color](50),
Quantity [COLOR=blue]int[/color], InvoiceNumber [COLOR=blue]varchar[/color](100), ClaimInvoiceID [COLOR=blue]int[/color], ProductID [COLOR=blue]int[/color])

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #SKU ([ID] [COLOR=blue]int[/color], ClaimID [COLOR=blue]int[/color], ProductCode [COLOR=blue]varchar[/color](50),
Quantity [COLOR=blue]int[/color], InvoiceNumber [COLOR=blue]varchar[/color](100), ClaimInvoiceID [COLOR=blue]int[/color], ProductID [COLOR=blue]int[/color])

 
[COLOR=blue]update[/color] [COLOR=blue]t[/color] [COLOR=blue]set[/color] quantity = s.SumQty
[COLOR=blue]FROM[/color]  #tempskus [COLOR=blue]t[/color]
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] productid, claiminvoiceid, SUM(quantity) [COLOR=blue]AS[/color] SumQty
                   [COLOR=blue]FROM[/color] #sku
                   [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] productid, claiminvoiceid) s
[COLOR=blue]on[/color] [COLOR=blue]t[/color].productid = s.productid and [COLOR=blue]t[/color].claiminvoiceid = s.claiminvoiceid




[COLOR=blue]DROP[/color] [COLOR=blue]table[/color] #tempSkus
[COLOR=blue]DROP[/color] [COLOR=blue]table[/color]  #SKU

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sodakotahusker]

Perfect. I must learn to harness the power of the derived table!
Thank you so much! Or should I say spaziba?

 

[bborissov]

Nope,
I am not Russian

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP