putting a sum from one table into another table

[jcoleman]

Hey Guys,

I'm trying to update table A with the sum of a column in table B. But the sum is by product number, not the bottom line sum.

Here's what I have but the compute part isn't working for the update. The select statement works good without the update part.

update [SFR Testing for MMR].dbo.[Tmp Transfer Table]
set [Pre-Month Sales Cnt] = (select [Short Sku], Produit, [Qté facturée]
 				from [TSC05].[OMNIF_A].dbo.[Fact ventes détail] iv inner join [SFR Testing for MMR].dbo.[Tmp Transfer Table] tt 
				on iv.Produit = (substring(tt.[Short Sku],2,6)+right((tt.[Short Sku]),1))
  				where [Date comptable] = '2005/02/17' and 
        			Produit = '2167604'
  				order by Produit 
				compute sum([Qté facturée]) by Produit)

It's proably not to hard but I'm still winging it.

 

[donutman]

I'll let you make the substitutions:

[Blue]UPDATE[/Blue] A
   [Blue]SET[/Blue] SumCol[Gray]=[/Gray]SumAmt
   [Blue]FROM[/Blue] A [Blue]INNER[/Blue] [Gray]JOIN[/Gray] 
      [Gray]([/Gray][Blue]SELECT[/Blue] ProductID[Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]TheCol[Gray])[/Gray] SumAmt
          [Blue]FROM[/Blue] B [Blue]GROUP[/Blue] [Blue]BY[/Blue] ProductID[Gray])[/Gray] DB
      [Blue]ON[/Blue] A.ProductID[Gray]=[/Gray]DB.ProductID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]