Microsoft KB Archive/90477

= INFO: Aggregates and SET Clause in UPDATE Statement =

Article ID: 90477

Article Last Modified on 2/14/2005

-

APPLIES TO


 * 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

-



This article was previously published under Q90477



SUMMARY
In an UPDATE statement, an aggregate function cannot appear directly in the SET list. For example, an attempt to execute a query such as,

UPDATE SET = count(*) FROM WHERE 

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 as above, there exists an ambiguity as to whether the associated WHERE clause qualifies the rows to be updated or it qualifies the rows on which to apply the aggregate function. The correct way to do this is:

UPDATE SET = (select count(*) from                        where ) FROM WHERE.

This is in accordance with ANSI Specification.

Additional query words: 4.20

Keywords: kberrmsg kbinfo KB90477

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.