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

ID: Q179163

The information in this article applies to:

Microsoft SQL Server, version 6.5 Service Pack 2, 3, and 4

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

Additional query words: subquery crltd crrltd SUM error set null sp sp1 sp2
sp3 sp4 st proc stproc

Keywords : SSrvGen kbbug6.50.sp2 kbbug6.50.sp3 kbbug6.50.sp4
Version : WINNT:6.5
Platform : winnt
Issue type : kbbug