Microsoft KB Archive/833376

= A DELETE statement with a join that involves views may not delete all the target rows in SQL Server 2000 Service Pack 3 =

Article ID: 833376

Article Last Modified on 6/1/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 3

-





SYMPTOMS
When you run a Transact-SQL DELETE statement with a join that involves a table and one or more views, you may notice that not all the rows that satisfy the join condition in the Transact-SQL DELETE statement are deleted.



RESOLUTION
To resolve the problem, install Security Patch MS03-031 for SQL Server 2000 on the computer that is running Microsoft SQL Server 2000.

To download Security Patch MS03-031 for SQL Server 2000 (32-bit), visit the following Microsoft Web site:

SQL Server 2000 (32-bit) Security Patch MS03-031



Steps to reproduce the behavior
To reproduce the behavior, follow these steps:  Start SQL Query Analyzer. Connect to the pubs database in SQL Server.  Run the following Transact-SQL statements: -- Create a test table and name it MyTable create table MyTable (FirstCol int) go -- Create a view and name it FirstView create view FirstView as select MyTable.FirstCol as FirstCol, T.Col1 as SecondCol from MyTable, (select 1 as Col1 union all select 2) T go -- Create a view and name it SecondView create view SecondView as   select FirstView.FirstCol as SecondCol, FirstView.SecondCol as FirstCol from FirstView, MyTable go -- Insert sample rows in the MyTable table insert into MyTable values (1) insert into MyTable values (2) insert into MyTable values (2) go -- Delete rows in the MyTable table by joining the table with the FirstView and the SecondView views delete from MyTable from FirstView, SecondView where MyTable.FirstCol = FirstView.SecondCol and SecondView.FirstCol = FirstView.FirstCol and SecondView.SecondCol = 1 go -- Verify the rows in the MyTable table select * from MyTable go Note One row is not deleted from the MyTable table. 

