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 2012 Forums
 Transact-SQL (2012)
 Update with subquery values

Author  Topic 

Gregorys05
Starting Member

5 Posts

Posted - 2013-06-20 : 11:12:58
Hi all,
I have the below query


DECLARE @LoanValue decimal(38,20)
DECLARE @APR decimal(20,15)
DECLARE @FullTerm int
DECLARE @CurrentPeriod int
DECLARE @ALAGU decimal(20,15)

SET @LoanValue = -20560
SET @APR = 0.11 --11%
SET @FullTerm = 128 --300 months = 25 years
SET @CurrentPeriod = 1+1 --For which payment period do you want the payment breakdown?
Set @ALAGU = 0.8578 -- Allocated Loan Amount Gross-up

DECLARE @PMT decimal(38,2)
DECLARE @FV decimal(38,2)
DECLARE @IPMT decimal(38,2)
DECLARE @PPMT decimal(38,2)

Update MA
Set MA.[Allocated Loan Amount @ Sep-13] = Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, @LoanValue, 0, 0)/(@APR/12))
From dbo.TblMaster MA


Which works fine with static values but i want to change the @LoanValue to not be a static value of -20560 but to be the value from


Select MA.Loanvalue from dbo.tblmaster where MA.Serial = 'Serial from update query'


The issue i am having is that the Update query will update of every row within my table but how to do tell the @loanvalue to be the MA.loanvalue that matches the right serial number.


E.g

Serial - LoanValue - [Allocated Loan Amount @ Sep-13]
1 -2000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -2000, 0, 0)/(@APR/12))
2 -5000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -5000, 0, 0)/(@APR/12))
3 -3000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -3000, 0, 0)/(@APR/12))
4 -4000 Convert(decimal(38,0),dbo.IPMT(@APR/12.0, @CurrentPeriod, @FullTerm, -4000, 0, 0)/(@APR/12))


Please help

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-20 : 11:16:48
Do you mean dynamically setting up @loanValue, like this?

SET @LoanValue = -20560 (Select MA.Loanvalue from dbo.tblmaster where MA.Serial = 'Serial from update query') -- Note that the result of Select statment should be one row/value .. else it will throw error

Cheers
MIK
Go to Top of Page
   

- Advertisement -