This article was previously published under Q90477
This article has been archived. It is offered "as is" and will no longer be updated.
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).
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>.
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