BUG: Query UPDATE Fails with IGNORE_DUP_KEY Set

 Last reviewed: April 8, 1997
Article ID: Q105337
The information in this article applies to:
  • Microsoft SQL Server version 4.2a for OS/2
  • Microsoft SQL Server, versions 4.2, 6.0, and 6.5
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 will fail if it will cause a duplicate row in the table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server versions 4.2, 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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%"
will fail 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 : kbprg SSrvServer SSrvWinNT
Version : 4.2a | 4.2 6.0 6.5
Platform : OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.