May 21, 2010 at 10:06 pm
What I am trying to do is populate data into column (trcd,transferamt) by doing an update to the #acct table joining it with the #trcd table. I do not get anything returned for those two columns
---------Create ACCT table----------------
Create Table #acct(
appl varchar(2) NULL DEFAULT NULL,
acct decimal(12,0) NULL DEFAULT NULL,
MatDate smalldatetime NULL DEFAULT NULL,
type smallint NULL DEFAULT NULL,
bal decimal(12,2) NULL DEFAULT NULL,
status tinyint NULL DEFAULT 3,
currenttype smallint NULL DEFAULT 0,
PrevTerm smallint NULL DEFAULT NULL,
NewTerm smallint NULL DEFAULT 0,
trcd varchar(10) NULL DEFAULT NULL,
tranferamt decimal (12,2) Null Default Null
)
Create table #TRCD(
appl varchar(2) NULL DEFAULT NULL,
acct decimal(12,0) NULL DEFAULT NULL,
trcd char(4) Null Default Null,
bal decimal(12,2) NULL DEFAULT NULL
)
Insert Into #TRCD (appl,acct,trcd)
SELECT svhist_appl_code,
svhist_acct_nbr,
svhist_trcd
from lmrsql49p.misdbw021.dbo.sv_history
where svhist_appl_code = '02'
and substring(svhist_trcd,1,3) in ('c07','c08','dw8','zc7','zc8')
and svhist_post_dt = '5/03/10'
------Update #TRCD---------
Update #TRCD
Set bal = cd.bibal
from lmrcbap3p.misdbw021_dw.dbo.cb_cd_daily cd, #trcd t
where cd.acct = t.acct
and cd.appl = t.appl
Update #acct
Set trcd = t.trcd, bal = t.bal
From #trcd t , #acct a
Where a.appl = t.appl
and a.acct = t.acct
May 23, 2010 at 9:38 am
Let me rephrase my question. Can we update an existing table by doing a join?
Update #TRCD
Set bal = cd.bibal
from lmrcbap3p.misdbw021_dw.dbo.cb_cd_daily cd, #trcd t
where cd.acct = t.acct
and cd.appl = t.appl
Update #acct
Set trcd = t.trcd, bal = t.bal
From #trcd t , #acct a
Where a.appl = t.appl
and a.acct = t.acct
May 23, 2010 at 10:09 am
Yes, you can do that. I'd switch to using the new join syntax and not the old ANSI style stuff though. That would make that query look closer to:
Update #acct -- You can use just a here as well since the table has been aliased. Either is fine.
Set trcd = t.trcd,
bal = t.bal
From #acct a
INNER JOIN #trcd t ON a.appl = t.appl and a.acct = t.acct
May 23, 2010 at 11:56 am
Thanks Geradin!
I will try it tomorrow and let you know.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply