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
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 xxx (keynumber nvarchar(10) primary key clustered, comment nvarchar(20))
Insert Pseudo Data
insert into xxx (keynumber,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
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
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.
If the column
keynumber would have been an
int, then it would have been impossible to enter
'12345 into the table.