SQL Server update statement error

Question

I am writing a script to update the duplicate contact and all its tables where it is referenced.

One of the update statements that I have is the following:

/* update the contactid, and the compcontactid on the compcontact table */
UPDATE cmpc
SET cmpc.contactid = tt.contactid,
    cmpc.compcontactid = (SELECT MAX(cc.compcontactid) 
                          FROM compcontact cc 
                          INNER JOIN @tempDupContacts tdup ON tdup.contactid = cc.contactid 
                          INNER JOIN @tempTable tt ON tt.namefml = tdup.namefml) 
FROM compcontact cmpc
INNER JOIN @tempDupContacts tdup ON tdup.contactid = cmpc.contactid
INNER JOIN @tempTable tt ON tt.namefml = tdup.namefml

However, when I run the script (script is way tooo long to post here), I get the following error:

Msg 2601, Level 14, State 1, Line 255
Cannot insert duplicate key row in object 'dbo.compcontact' with unique index 'XPKcompcontact'. The duplicate key value is (A000UZCU, A00JTCAP, X00GM2NF).

Can anyone explain why this is happening, and what the fix would be?

Is this because It is attempting to update the value that has the

sion_corn · Accepted Answer · 2014-05-01 18:04:23Z

4

You are attempting to update a unique key value to a value that already exists in the column. This is not allowed, since each value in the unique key must be unique.

answered May 1, 2014 at 18:04

sion_corn

3,1518 gold badges42 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3188781 · Accepted Answer · 2014-05-02 00:56:55Z

0

As @sion_corn said, you're trying to update a column having unique key constraint. Try adding a WHERE clause in your update statement to exclude the value which already exists.

For example:

UPDATE cmpc
SET cmpc.contactid = tt.contactid,
    cmpc.compcontactid = (SELECT MAX(cc.compcontactid) 
                      FROM compcontact cc 
                      INNER JOIN @tempDupContacts tdup ON tdup.contactid = cc.contactid 
                      INNER JOIN @tempTable tt ON tt.namefml = tdup.namefml     
                      WHERE cc.compcontactid <> cmpc.compcontactid) 
FROM compcontact cmpc
INNER JOIN @tempDupContacts tdup ON tdup.contactid = cmpc.contactid
INNER JOIN @tempTable tt ON tt.namefml = tdup.namefml 
WHERE cmpc.contactid <> tt.contactid

answered May 2, 2014 at 0:56

user3188781

1 Comment

Greg Over a year ago

By adding a where clause it will stop the error, however there can be value in that error coming back so that you know your data hasn't been saved.

Collectives™ on Stack Overflow

SQL Server update statement error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related