INFO: Aggregates and SET Clause in UPDATE Statement

Article translations Article translations
Article ID: 90477 - View products that this article applies to.
This article was previously published under Q90477
Expand all | Collapse all

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 <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 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 <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.

Properties

Article ID: 90477 - Last Review: February 14, 2005 - Revision: 3.3
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
Keywords: 
kberrmsg kbinfo KB90477

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com