Microsoft KB Archive/179163

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

Article ID: 179163

Article Last Modified on 10/3/2003

-

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

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

Keywords: kbbug kbsqlserv650sp2bug kbsqlserv650sp3bug kbsqlserv650sp4bug KB179163

-

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

© Microsoft Corporation. All rights reserved.