Violation of primary key on update

Question

Violation of primary key on update

jack-64275

SSCrazy

Points: 2017
More actions
July 17, 2008 at 8:29 am

#188292

Hello All,
I have a table with a PK:
PRIMARY KEY CLUSTERED
(
[UnitType] ASC,
[ClassID] ASC,
[InvtId] ASC,
[FromUnit] ASC,
[ToUnit] ASC
)
I am trying to update the FromUnit and ToUnit columns as follows:
UPDATE INUnit
SET FromUnit = newUC
from xUoMConversion
inner join INUnit on FromUnit = CurrentUC
UPDATE INUnit
SET ToUnit = newUC
from xUoMConversion
inner join INUnit on ToUnit = CurrentUC
The xUoMConversion table has only two columns - CurrentUC and NewUC.
When I run these updates I get:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'INUnit0'. Cannot insert duplicate key in object 'dbo.INUnit'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'INUnit0'. Cannot insert duplicate key in object 'dbo.INUnit'.
The statement has been terminated.
I know this is due to updating the PK but I'm not sure how to get around this. Any suggestions would be appreciated.
Thanks!
FYI... two ideas I have are droping the PK, running the update, recreating the PK or export the data, change the data in a spreadsheet, and import it / overwrite existing data. Hoping there is a better way.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Without knowing more about your data, I'm guessing its because you are doing the update in 2 statements, and that if you update both at the same time, you won't get a duplicate:-

UPDATE INUnit

SET FromUnit = a.newUC,

ToUnit = b.newUC

from xUoMConversion

inner join INUnit a on FromUnit = a.CurrentUC

inner join INUnit b on ToUnit = b.CurrentUC

jack-64275 SSCrazy Points: 2017 More actions · Answer 2

Ian,

Thanks for the suggestion... guess i should have checked the data before posting. There are actually dups.

Sorry to waste time.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

even if you dropped the PK and loaded the data as soon as you try and re create the PK it would fail anyway

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉