UPDATE QUERY INNER JOIN HELP

[swoodring]

I have a table called Tmpstockledger that I am trying to update stockledger with. Ths issue is TMPStockledger has multiple records based on the join and its only updating with values from first record in TMPSTOCKLEDGER. How do I get it to update with both values. I'm updated a bunch of values I'll just post one for an example:

UPDATE StockLedgerbackup
SET
CurrentInventoryUnits =T.CurrentInventoryUnits + S.CurrentInventoryUnits

FROM StockLedger S

inner JoinTmpStockLedger T
on
S.ClassCode = T.ClassCode and
S.Vendornum = t.VendorNum and
S.StoreCode = T.StoreCode and
S.WeekendingDate = T.WeekendingDAte

WHERE
S.ClassCode = T.ClassCode and
S.Vendornum = t.VendorNum and
S.StoreCode = T.StoreCode and
S.WeekendingDate = T.WeekendingDAte
and T.weekendingdate = '2005-02-26'
and T.Storecode = 521

 

[vongrunt]

T.CurrentInventoryUnits is number of some kind, and you want to increase S.CurrentInventoryUnits by sum of all T.Inv...units?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[swoodring]

that would be correct. that is what I am trying to accomplish.

 

[vongrunt]

Then try this:

UPDATE S
SET CurrentInventoryUnits = CurrentInventoryUnits + T.SumCurrentInventoryUnits
FROM StockLedgerbackup
INNER JOIN
(	SELECT ClassCode, VendorNum, StoreCode, WeekendingDate,
		SUM(CurrentInventoryUnits) AS SumCurrentInventoryUnits
	FROM TmpStockLedger
	WHERE WeekendingDate = '2005-02-26'
		AND Storecode = 521
	GROUP BY ClassCode, VendorNum, StoreCode, WeekendingDate
) T
ON 
    S.ClassCode = T.ClassCode and
    S.VendorNum = T.VendorNum and
    S.StoreCode = T.StoreCode and
    S.WeekendingDate = T.WeekendingDate

I'm not sure this is exactly what you want but general idea is clear... derived table T returns sums, the rest is all about inner join...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)