Search code examples
sql-updateon-duplicate-key

Sql update: continue updating after duplicate key error (transact-SQL)

I have the following script:

update myTable set FieldValue = 1 where FieldValue = 2

Now lets assume that I have 10 entries in mytable and when updating the 5th element, I get the duplicate key insertion error:

Cannot insert duplicate key row in object 'dbo.mytable' with unique index. The duplicate key value is (FieldValue).

and the updating of the elements which had to be updated after the 5th one is not done.

It would be ok for me to do nothing if the error occurs and to step over the updating of the element which cannot be updated because of getting the error above.

How can I continue the updating of the rest of the elements after this kind of error?

Solution

  • Use the IGNORE keyword like so:

    update IGNORE myTable set FieldValue = 1 where FieldValue = 2;

    If a record update fails (e.g. because of a unique key contraint), it will be skipped and the others will continue.