0

I have a table that uses serial numbers as the primary key in a database. We had a bad import of some serial numbers, where a single apostrophe was included at the beginning of the field.

I attempted to remove it using the statement

update [xxx].[dbo].[xxx] set [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','')

When I did, I got the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'xxx$PrimaryKey'. Cannot insert duplicate key in object 'dbo.xxx'. The duplicate key value is (123456789).

Is there a way that I can update this field and remove the single apostrophe without violating the constraint?

  • 2
    One small tip, include a WHERE clause that filters down to just the bad records, that way you're not doing a needless UPDATE for the good ones. – BCM Apr 14 at 15:50
1

We would need the table definition for a definite answer. It looks like that when you remove the single apostrophe from the serial number, that that value is then the same as an already existing value. Hence the error message.

It looks like the import file was corrected and imported again, resulting in multiple values containing, for example, values similar to 12345 and '12345.

Solution

I would try and search for the near identical primary keys first and see if you can delete them.

I created a dummy scenario with db<>fiddle to show you how it would work.

Create Table

create table xxx (keynumber nvarchar(10) primary key clustered, comment nvarchar(20)) 

Insert Pseudo Data

insert into xxx (keynumber,comment) 
values 
('12345','First Comment'), 
('''12345','Second Comment'), 
('12346','Third Comment'), 
('''12346','Fourth Comment'),
('123457','Fith Comment')

SELECT Psuedo Duplicates

SELECT x1.keynumber, x2.keynumber 
FROM [xxx].[dbo].[xxx] as x1 
     JOIN [xxx].[dbo].[xxx] as x2
     ON '''' + x1.keynumber = x2.keynumber

Results

keynumber | keynumber
:-------- | :--------
12345     | '12345   
12346     | '12346   

You can then either deleted the duplicates or rename them (see Alternative)

Once you have cleaned up the duplicates, you will be able to run your UPDATE.... Before that you have some manual labour to do.

Alternative / Renaming

Instead of just removing the apostrophe, add an additional value to the keynumber values that have an apostrophe. E.g. add CHK to the keynumber

UPDATE [xxx].[dbo].[xxx] 
SET [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','CHK')

This way you will be able to see which values have to be checked.

Observations

If the column keynumber would have been an int, then it would have been impossible to enter '12345 into the table.

| improve this answer | |
  • Thank you, this proved to be the case. There were duplicates of the entries. And I agree, it should have been set up as an int, or better with a separate unique ID. – JohnP Apr 15 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.