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.
The query plan may not include a necessary Table Spool below the UPDATE operator, which is necessary to provide Halloween Protection.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
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 theMicrosoft Knowledge Base:
301511 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.
To work around this problem:
Force a loop join by using OPTION(LOOP JOIN).
Add an index on the updated columns.
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.
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 oncreate table test(id int, pid int, fn varchar(256), rn varchar(8))godeclare @c intset @c = 1insert into test values(0, NULL, 'root', 'root')while @c < 10begin insert into test values(@c, @c-1, NULL, cast(@c as varchar(8))) set @c = @c + 1endcreate unique clustered index idx_c_id on test(id)goupdate testset fn = parent.fn + '/' + test.rnfrom 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: