BUG: Regression: Message 515 When Doing UPDATE with ISNULL, an Aggregate, and a Correlated Subquery (179163)

The information in this article applies to:

Microsoft SQL Server 6.5 Service Pack 2
Microsoft SQL Server 6.5 Service Pack 3
Microsoft SQL Server 6.5 Service Pack 4

This article was previously published under Q179163

BUG #: Windows NT: 17743 (6.50)

SYMPTOMS

With SQL Server 6.5 Service Pack 2 and later, updating a column defined as NOT NULL by using a SELECT statement with ISNULL, an aggregate, and a correlated subquery will generate msg 515 (Attempt to insert the value NULL into column) when there are additional rows in the target table without corresponding rows in the source table.

WORKAROUND

To work around this problem, do any one of the following;

Remove the GROUP BY clause.

-or-
Add FROM and WHERE clauses for the UPDATE statement.

-or-
Guarantee that there are no additional rows in the target table without corresponding rows in the source table, by adding constraints, triggers, or stored procedures to enforce referential integrity.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The following is an example:

   UPDATE
       TblX
   SET
       ColB = (SELECT
                          ISNULL(SUM(ColC),0)
                      FROM
                          TblY
                      WHERE
                          TblX.ColA = TblY.ColA
                      GROUP BY
                          TblY.ColA)

This works fine in SQL Server 6.0, 6.5 (release version), and 6.5 Service Pack 1. However, it fails on SQL Server 6.5 Service Packs 2, 3, and 4, as well as on SQL Server build 6.50.285.

The example script above will work successfully if any of the following is true:

Every row in the target table has at least one row in the source table.

-or-
The GROUP BY clause is removed from the subquery.

-or-
A FROM and WHERE clause are specified for the UPDATE statement (outside of the subquery).

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbBug kbSQLServ650sp2bug kbSQLServ650sp3bug kbSQLServ650sp4bug KB179163 kbAudDeveloper