Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/105337

From BetaArchive Wiki
Knowledge Base


BUG: Query UPDATE Fails with IGNORE_DUP_KEY Set

Article ID: 105337

Article Last Modified on 12/3/2003



APPLIES TO

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 4.2 for OS/2



This article was previously published under Q105337

BUG# OS/2: 1777 (4.2a)
NT: 505 (4.2), 14895 (6.0/6.5)
        

SYMPTOMS

On page 226 of the "Transact-SQL Reference" for SQL Server for Windows NT (or page 69 of the "Language Reference Guide" for OS/2), it states:

If IGNORE_DUP_KEY is set and you give an UPDATE or INSERT statement that creates duplicate keys, the row that causes the duplicates is not added or changed. In fact, in the case of UPDATE , the row is discarded.
...However, if the UPDATE or INSERT attempt affects multiple rows, the other rows are added or changed as usual.


When an UPDATE is performed, SQL Server does not behave as explained above. Instead, the UPDATE fails if it will cause a duplicate row in the table.

STATUS

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

MORE INFORMATION

For example, table test (name char(10), age int NULL) has a unique clustered key with IGNORE_DUP_KEY on column age:

   Name               Age
   ----------------------

   karl               19
   smith              20
   johns              24
   mary               25
                

The following query:

     update test
     set age=age+4
     where name not like "%johns%"
                

fails with the errors:

duplicate key was ignored

-and-


0 rows affected.

According to the manual, it should discard the row smith and update the other two rows (karl and mary) as in the following:

   name             age
   --------------------

   karl             23
   johns            24
   mary             29
                


Additional query words: update duplicates Windows NT

Keywords: kbbug kbprogramming kbpending KB105337