July 17, 2008 at 8:29 am
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.
July 17, 2008 at 8:42 am
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
July 17, 2008 at 8:57 am
Ian,
Thanks for the suggestion... guess i should have checked the data before posting. There are actually dups.
Sorry to waste time.
July 17, 2008 at 3:04 pm
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" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply