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.
Author |
Topic |
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2012-01-10 : 06:30:17
|
The inner query returns more than 1 row and for each row the value has to be updated in the outer query.Currently the query is updating only for the 1st row returned by the inner query.How to achieve this?UPDATE FloatBalSET FloatBal.AvlBalance = case when AllLoads.LoadType like '%Credit' then (FloatBal.AvlBalance - LoadBal.TotalLoadAmt) when AllLoads.LoadType like '%Debit' then (FloatBal.AvlBalance + LoadBal.TotalLoadAmt)ENDFROM dbo.[EPP_Fin_FloatBalance] FloatBalINNER JOIN (SELECT AllLoad.CardProduct,sum(LoadAmt) AS TotalLoadAmt,allload.LoadType FROM [AllLoads] AllLoad LEFT JOIN dbo.EPP_Fin_FloatBalance_Log FloatBalLog ON FloatBalLog.ID = AllLoad.ID AND FloatBalLog.CardProduct = AllLoad.CardProduct WHERE FloatBalLog.ID IS NULL AND AllLoad.CardProduct IN ('FP51') AND AllLoad.LoadType = 'OBI BalAdjust Debit' GROUP BY AllLoad.CardProduct,AllLoad.LoadType ) LoadBal ON LoadBal.[CardProduct] = FloatBal.[ProductCode]COLLATE Latin1_General_CI_AIWHERE FloatBal.ProductCode COLLATE Latin1_General_CI_AI IN ('FP51') |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 06:45:39
|
do you mean you've more than one row per CardProduct in dbo.[EPP_Fin_FloatBalance] table? probably you can post some sample data to illustrate the scenario and somebody will be able to help you out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2012-01-10 : 07:32:21
|
The inner querySELECT AllLoad.CardProduct,sum(LoadAmt) AS TotalLoadAmt,allload.LoadTypeFROM [AllLoads] AllLoadLEFT JOIN dbo.EPP_Fin_FloatBalance_Log FloatBalLogON FloatBalLog.ID = AllLoad.IDAND FloatBalLog.CardProduct = AllLoad.CardProduct WHERE FloatBalLog.ID IS NULLAND AllLoad.CardProduct IN ('FP51')AND AllLoad.LoadType = 'OBI BalAdjust Debit'GROUP BY AllLoad.CardProduct,AllLoad.LoadTypewould return the rowsCardProduct TotalLoadAmt LoadTypeFP51 50.00 OBI BalAdjust CreditFP51 25.00 OBI BalAdjust DebitThe dbo.[EPP_Fin_FloatBalance] has one row per card productAvlBalance CurrencyCode ProductCode110.00 GBP FP51 So after the update query the avlBalance in dbo.[EPP_Fin_FloatBalance] should be 85.Thanks,Sandesh |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-10 : 07:45:11
|
This?UPDATE FloatBalSET FloatBal.AvlBalance = FloatBal.AvlBalance + LoadBal.TotalLoadAmtFROM dbo.[EPP_Fin_FloatBalance] FloatBalINNER JOIN ( SELECT AllLoad.CardProduct, sum(case when AllLoads.LoadType like '%Credit' then LoadAmt * -1 else LoadAmt end) AS TotalLoadAmt, allload.LoadType FROM [AllLoads] AllLoad LEFT JOIN dbo.EPP_Fin_FloatBalance_Log FloatBalLog ON FloatBalLog.ID = AllLoad.ID AND FloatBalLog.CardProduct = AllLoad.CardProduct WHERE FloatBalLog.ID IS NULL AND AllLoad.CardProduct IN ('FP51') AND AllLoad.LoadType = 'OBI BalAdjust Debit' GROUP BY AllLoad.CardProduct,AllLoad.LoadType)LoadBalON LoadBal.[CardProduct] = FloatBal.[ProductCode]COLLATE Latin1_General_CI_AIWHERE FloatBal.ProductCode COLLATE Latin1_General_CI_AI IN ('FP51') No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2012-01-11 : 06:29:15
|
Spot on. Thanks for your help.Thanks,Sandesh |
|
|
|
|
|
|
|