SQL Server : check for existing entry before UPDATE

Question

I am consolidating incorrect entries in a table. Specifically for this online shop there are manufacturers and their article numbers. Sometimes manufacturers have been created twice with two different names, such as "VHY" and "Vishay" which should be the same. So I want to consolidate this into one correct manufacturer.

I have the following SQL statement:

/** Old Manufacturer Name **/
DECLARE @old varchar(100) = 'VHY';
/** New Manufacturer Name **/
DECLARE @new varchar(100) = 'VISHAY';

/** Updating the table **/
UPDATE [ESO65].[dbo].[ESO$Manufacturer Item]
SET [Manufacturer Code] = @new
WHERE [Manufacturer Code] = @old

Unfortunately I get the error message:

Violation of PRIMARY KEY constraint 'ESO$Manufacturer Item$0'. Cannot insert duplicate key in object 'dbo.ESO$Manufacturer Item'.

This is because there are duplicate entries where Manufacturer Code = VHY, Manufacturers Item No_= TEST and Manufacturer Code = Vishay, Manufacturers Item No_= TEST. So this is the duplicate object violation.

How do I delete all entries for old where there is double entries and then rename the old to new?

enter image description here

hello. I don't quite understand. Could you maybe post the table schema so we can follow along? — Mike M, May 09 '14 at 10:47
We basically have a table with [Manufacturer Code], [Manufacturers Item No_] — Dominik, May 09 '14 at 11:05
This is not a duplicate, it is a different question. The logic is not the same — t-clausen.dk, May 09 '14 at 11:36

score 1 · Answer 1 · answered May 09 '14 at 11:45

First you have to delete duplicate records. Following script will delete duplicates. (According to given information)

DELETE FROM [ESO65].[dbo].[ESO$Manufacturer Item] AS DeleteItem
WHERE DeleteItem.[Manufacturer Code] = @old
AND DeleteItem.[Manufacturer Item] IN
(
  -- SELECT DUPLICATES
  SELECT DuplicateItem.[Manufacturer Item]
  FROM [ESO65].[dbo].[ESO$Manufacturer Item] AS DuplicateItem
  WHERE (DuplicateItem.[Manufacturer Code] = @old OR DuplicateItem.[Manufacturer Code] = @new)
  GROUP BY DuplicateItem.[Manufacturer Item]
  HAVING COUNT(*)>1
)

Then you can run your update script.

score 1 · Accepted Answer · answered May 09 '14 at 12:28

This script will only delete the existing entries. To make room for the UPDATE

/** Old Manufacturer Name **/
DECLARE @old varchar(100) = 'VHY';
/** New Manufacturer Name **/
DECLARE @new varchar(100) = 'VISHAY';    

BEGIN TRANSACTION t
  DELETE t1
  FROM [ESO$Manufacturer Item] t1
  JOIN [ESO$Manufacturer Item] t2
  on t1.[Manufacturer Code] = @old and 
  t2.[Manufacturer Code] = @new
  and t1.[Manufacturers Item No_] = t2.[Manufacturers Item No_]

  UPDATE [ESO$Manufacturer Item]
  SET [Manufacturer Code] = @new
  WHERE [Manufacturer Code] = @old
COMMIT TRAN T;

SQL Server : check for existing entry before UPDATE

2 Answers2