Help and Support

FIX: Update With Self Join May Update Incorrect Number Of Rows

View products that this article applies to.
Article ID:285870
Last Review:October 28, 2003
Revision:3.0
This article was previously published under Q285870
BUG #: 101111 (SQLBUG_70)
BUG #: 351761 (SHILOH_bugs)
On This Page

SYMPTOMS

An UPDATE operation may not modify the correct number of rows if the following conditions are met:
•Your query updates a column, ColA, from a table TabA.
•The query references TabA again, using an alias TabB.
•There is a reference to TabB.ColA somewhere in the query.
•None of the columns being updated in TabA are indexed columns.
•A hash or merge join is used.

Back to the top

CAUSE

The query plan may not include a necessary Table Spool below the UPDATE operator, which is necessary to provide Halloween Protection.

Back to the top

RESOLUTION

SQL Server 2000

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 (http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Back to the top

SQL Server 7.0

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
301511 (http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack


NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix should have the following file attributes or later: 
   Version      File name       Platform
   -------------------------------------

   7.00.978     s70978i.exe     x86
   7.00.978     s70978a.exe     Alpha
NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

Back to the top

WORKAROUND

To work around this problem:
•Force a loop join by using OPTION(LOOP JOIN).

-or-

•Add an index on the updated columns.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

SQL Server 2000
This problem was first corrected in SQL Server 2000 Service Pack 1.

SQL Server 7.0
This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

Back to the top

MORE INFORMATION

Halloween protection is needed to prevent a situation where the physical location of a row within a table changes due to an UPDATE operation. As a result, the same row may be revisited multiple times within the context of a single logical operation, which should not occur. If the conditions for this particular bug are met, the SQL Server optimizer may not build a plan with appropriate Halloween protection.

The following example demonstrates the problem: 
set nocount on
create table test(id int, pid int, fn varchar(256), rn varchar(8))
go
declare @c int
set @c = 1
insert into test values(0, NULL, 'root', 'root')
while @c < 10
begin
   insert into test values(@c, @c-1, NULL, cast(@c as varchar(8)))
   set @c = @c + 1
end
create unique clustered index idx_c_id on test(id)
go
update test
set fn = parent.fn + '/' + test.rn
from test(index=0) , test parent(index=0)
where test.pid = parent.id and test.fn is NULL --  and parent.fn <> ''
option(hash join,force order)
Here are the correct contents of the table after the update:

-- CORRECT results: 
id          pid         rn       fn                                                                                                                                                                                                                                                               
----------- ----------- -------- ---- 
0           NULL        root     root
1           0           1        root/1
Here are the results you obtain prior to applying the fix:

-- INCORRECT results: 
id          pid         rn       fn                                                                                                                                                                                                                                                               
----------- ----------- -------- ----
0           NULL        root     root
1           0           1        root/1
2           1           2        root/1/2
3           2           3        root/1/2/3
4           3           4        root/1/2/3/4
5           4           5        root/1/2/3/4/5
6           5           6        root/1/2/3/4/5/6
7           6           7        root/1/2/3/4/5/6/7
8           7           8        root/1/2/3/4/5/6/7/8
9           8           9        root/1/2/3/4/5/6/7/8/9

Back to the top

APPLIES TO
•Microsoft SQL Server 2000 Standard Edition
•Microsoft SQL Server 7.0 Standard Edition

Back to the top

Keywords: 
kbbug kbfix KB285870

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.