question

Bobby avatar image
Bobby asked

Can we do an update columns in one table we created with data from another table by doing a join?

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

sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
malpashaa avatar image
malpashaa answered

Actually it is hard to figure out the problem in your update statements. But I think the problem is that your are not using the alias of the tables you want to update so for example the second update should be:

            
UPDATE a            
   SET a.trcd = t.trcd,            
       a.bal = t.bal            
  FROM #trcd t, #acct a            
 WHERE a.appl = t.appl            
   AND a.acct = t.acct            

Notice the usage of the alias (a), and you should apply the same thing to the other update statement.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.