BUG: UPDATE of Table Column with a FOREIGN KEY Constraint May Fail with Msg 8623

ID: Q214818

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 53943 (SQLBUG_70)

SYMPTOMS

Attempting to update a column using a declared variable may fail if that column has a FOREIGN KEY (FK) constraint and the FROM clause references multiple tables. In such cases, the following error message occurs:

Server: Msg 8623, Level 16, State 2, Line 8
Internal Query Processor Error: The query processor could not produce a query plan.

For example, the following would cause the error:


declare @c1 int
select @c1 =
   update secondary_tbl set c1 = @c1
   from secondary_tbl p,child_1 t1, child_2 t2
   where  p.s1 = t1.s1

WORKAROUND

To work around this problem, use CONVERT to cast the variable to its data type. In the example above, replace:


update secondary_tbl set c1 = @c1

with:


update secondary_tbl set c1 = convert(int,@c1)

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words: datatype fk fkey const err qp

Keywords : SSrvTran_SQL kbbug7.00
Version : winnt:7.0
Platform : winnt
Issue type : kbbug