BUG: UPDATE Statement Using a SELECT with Aggregates and JOIN May Generate Internal Error (295742)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 7.0

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 "More Information" 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

Modification Type:	Major	Last Reviewed:	10/17/2003
Keywords:	kbbug kbpending KB295742