Microsoft KB Archive/295742

= BUG: UPDATE Statement Using a SELECT with Aggregates and JOIN May Generate Internal Error =

Article ID: 295742

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q295742



BUG #: 353561 (SHILOH_BUGS)

BUG #: 101562 (SQLBUG_70)



SYMPTOMS
An UPDATE statement using a SELECT with aggregates (MIN or MAX) and an old-style JOIN may generate error 8624 in SQL Server 2000:

Server: Msg 8624, Level 16, State 16, Line 11

Internal SQL Server error.

The same query may generate the following error in SQL Server 7.0:

Server: Msg 8630, Level 17, State 38, Line 13

Internal Query Processor Error: The query processor encountered an unexpected error during execution.



WORKAROUND
There are two possible workarounds to this issue (see the example in the &quot;More Information&quot; section):   Add both tables to the FROM clause: UPDATE t3 SET   c3 = (SELECT max(c2) FROM t2 ,t1 WHERE   t2.c1 = t1.c1) FROM t1 -or-

  Use an ANSI join: UPDATE t3 SET   c3 = (SELECT max(c2) FROM t2 INNER JOIN t1 ON t2.c1 = t1.c1) FROM t1                   



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.

Microsoft has confirmed this to be a problem in SQL Server 7.0.



MORE INFORMATION
The following example illustrates the problem: CREATE TABLE t1 (   c1 CHAR(10))

CREATE TABLE t2 (   c1 CHAR(10),                              c2 int NULL)

CREATE TABLE t3 ( c3 int not null) --Problem: UPDATE t3 SET   c3 = (SELECT max(c2) FROM t2  WHERE   t2.c1 = t1.c1) FROM t1

DROP TABLE t1 DROP TABLE t2 DROP TABLE t3

Keywords: kbbug kbpending KB295742

-

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

© Microsoft Corporation. All rights reserved.