Update with subquery values - SQL Server Forums

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources