Update Statement Error - 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 2005 Forums

Transact-SQL (2005)

Update Statement Error

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-25 : 07:28:29

Dear All,

I am getting follwoing error with this query
UPDATE table SET
TOTAL_HR_WORKED=sum(convert(decimal(6,2),datediff(mi,MIN_Login_Time,MAX_Logout_Time)/60.00))

error :
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

Kabila
Starting Member

33 Posts

Posted - 2009-09-25 : 07:37:00

UPDATE b SET
TOTAL_HR_WORKED=(select sum(convert(decimal(6,2),datediff(mi,MIN_Login_Time,MAX_Logout_Time)/60.00)) from Tablename a where b.name=a.name)
from Tablename b

nalnait
Starting Member

14 Posts

Posted - 2009-09-25 : 07:39:03

error hint,you can't use sum() in update statment.
try other way.
e.g:
DECLARE @tempMoney money
SELECT @tempMoney=SUM()...FROM..
UPDATE TABLE SET TOTAL_HR_WORKED=@tempMONEY

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-25 : 07:47:29

thanks but what it is updating is not correct form example
Name Min Login Time Max Logot Time Hr Worked
A 1900-01-01 10:34:00.000 1900-01-01 20:00:00.000 9.4299999999999997

But if you calculate this actually he has worked 9:26 Min but it is updating 9:42 min

Please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 08:08:29

UPDATE b SET
TOTAL_HR_WORKED=(select sum(datediff(mi,MIN_Login_Time,MAX_Logout_Time)/60)+sum(datediff(mi,MIN_Login_Time,MAX_Logout_Time)%60)/100.0 from Tablename a where b.name=a.name)
from Tablename b

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources