FIX: Msg. 116 Doing Correlated Updates in a Stored Procedure (161223)

SYMPTOMS

Running a stored procedure that does a correlated UPDATE with trace flag 204 enabled may cause the following error:

Msg 116
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

The error only seems to occur consistently when running the procedure with trace flag 204 after the server is recycled (subsequent to creating the procedure).

The correlated UPDATE could be similar to the following:

   INSERT table1
   SELECT distinct x
   FROM table2
      UPDATE table1
      SET y = (SELECT SUM(z)
         FROM table2
         WHERE
   table1.x = table2.x
        )

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.