INFO: Aggregates and SET Clause in UPDATE Statement

This article was previously published under Q90477
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
In an UPDATE statement, an aggregate function cannot appear directlyin the SET list. For example, an attempt to execute a query such as,
   UPDATE <table name>   SET <column name> = count(*)   FROM <table names>   WHERE <condition List>				

generates the following message in 4.2:
An aggregate may not appear in the set list of an UPDATE statement.
(Msg 157, Level 15, State 1).
MORE INFORMATION
When an aggregate appears in the SET list of an UPDATE statement asabove, there exists an ambiguity as to whether the associated WHEREclause qualifies the rows to be updated or it qualifies the rows onwhich to apply the aggregate function. The correct way to do this is:
   UPDATE <table name>   SET <column name> = (select count(*) from <table name>                        where <aggregate condition>)   FROM <table names>   WHERE <update condition>.				

This is in accordance with ANSI Specification.
4.20
Properties

Article ID: 90477 - Last Review: 12/04/2015 09:18:58 - Revision: 3.3

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kberrmsg kbinfo KB90477
Feedback