slight issue with update query

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

Transact-SQL (2000)

slight issue with update query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 12:28:49

I need to get a field updated with a count, and am getting an error. What's the way to do this. Do I need to somehow put the count function into a subquery or something?

=============
Update calllog
set repnumreps = count (detail.callid) from calllog join detail on calllog.callid = detail.callid
where swernaccservsubcalltype = 'Ext-access' and Month(cast(faultstart as datetime)) = month(getdate())

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

Kokkula
Starting Member

41 Posts

Posted - 2010-08-05 : 05:10:43

Hello,

Try this....

Update C
SET repnumreps = B.Cnt
FROM calllog C
JOIN
(
SELECT count (detail.callid) Cnt, calllog.callid
FROM calllog
FROM detail ON calllog.callid = detail.callid
WHERE swernaccservsubcalltype = 'Ext-access'
AND MONTH(CAST(faultstart as datetime)) = MONTH(GETDATE())
) AS B ON B.callid = A.callid

Hope its helpful....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources