Microsoft KB Archive/274661

= FIX: Delete Operation May Fail to Delete Qualified Rows with a Self Referenced Subquery =

Article ID: 274661

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q274661



BUG #: 57857 (SQLBUG_70)



SYMPTOMS
A DELETE operation may fail to delete all the selected rows if you use a self-referencing subquery in the WHERE clause. For more information about the nature of the query, see the &quot;More Information&quot; section.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



Steps to Reproduce the Problem
  Run the following code on a Microsoft SQL Server server that has Service Pack 2 or earlier installed: use pubs go drop table x go create table x (id int,pid int) go insert into x values (1,0) insert into x values (2,1) insert into x values (3,2) go delete from x where pid = (select id from x where pid = 1) or id = 1 or pid = 1 go   Run the following code to check the results: select * from x RESULTS: The following output displays when you run the preceding code:

id         pid --- --- 3          2

(1 row(s) affected)

The expected result is 0 rows.



Keywords: kbbug kbfix kbcodesnippet KB274661

-

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

© Microsoft Corporation. All rights reserved.