BUG: Regr: Msg 515 When Doing UPDATE w/ISNULL, Agg. & Cor. Sbqry

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

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)

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