Microsoft KB Archive/833376

Knowledge Base

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

MORE INFORMATION

Steps to reproduce the behavior

To reproduce the behavior, follow these steps:

1. Start SQL Query Analyzer.
2. Connect to the pubs database in SQL Server.

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

REFERENCES

For more information about security patch MS03-031 for SQL Server 2000 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:

821277 MS03-31: Security Patch for SQL Server 2000 Service Pack 3

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

295039 FIX: Assertion raised when DELETE is performed on a table joined with a view

Keywords: kbdownload kbprb kbcode kbquery KB833376

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.