Update problem - 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 2000 Forums

SQL Server Development (2000)

Update problem

Author

Topic

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2008-11-10 : 05:13:29

Can any one help me with an update problem the following code builds a temp table then does an update. this works fine till I vue the final table the column I have just updated contains the same number. I am sure this is a school boy error but I cant see it.

My code is as follows

Create Table #AspectHandled
(
LogDate DateTime,
Offered Int,
)
Insert Into #AspectHandled (LogDate, Offered)
Select LogDate, Count (Disposition)

FROM [tblRawCMDudExTWCMCAspect]
Where Disposition = '2' or Disposition = '4' or Disposition = '5' or Disposition = '7' or Disposition = '9'
And Offered = '2' or Offered = '15'
And LogDate>=DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0)
Group By LogDate

Update tblCMDashAppAspect
Set Offered = #TA.Offered
From #AspectHandled As #TA

Whitmoj
If you are in a hurry you will never get there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:22:14

try like this & see

Create Table #AspectHandled
(
LogDate DateTime,
Offered Int,
)
Insert Into #AspectHandled (LogDate, Offered)
Select LogDate, Count (Disposition)

FROM [tblRawCMDudExTWCMCAspect]
Where (Disposition = '2' or Disposition = '4' or Disposition = '5' or Disposition = '7' or Disposition = '9' )
And (Offered = '2' or Offered = '15')
And LogDate>=DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0)
Group By LogDate


Update t
Set t.Offered = #TA.Offered
From #AspectHandled As #TA
JOIN tblCMDashAppAspect t
ON t.linkingcol=#TA.linkingcol

linkingcol are columns by which temp table and your table are related

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources