Can't update column values, it is associated with a clustered index?

Question

I am having some problems when trying to update column values, this column has a clustered index associated to it.

This is the update statement.

UPDATE  dbo.VentureXRef
SET     RefValue = REPLICATE('0',7 - LEN(RefValue)) + RefValue WHERE   LEN(RefValue) < 7

This is the error I get

Cannot insert duplicate key row in object 'dbo.VentureXRef' with unique index 'idx_WFHMJVXRef_RefValueByType'.

This is mytable definition

CREATE TABLE [dbo].[VentureXRef]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[RefValue] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RefValueTypeID] [int] NOT NULL,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__WFHMJoint__State__2AC11801] DEFAULT (' '),
[ClientID] [int] NOT NULL,
[DoingBusinessAs] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Disabled] [bit] NOT NULL CONSTRAINT [DF_VentureXRef_Disabled] DEFAULT (0),
[Username] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_VentureXRef_Username] DEFAULT (user_name()),
[DateDeleted] [datetime] NULL,
[DateLastModified] [datetime] NOT NULL CONSTRAINT [DF_VentureXRef_DateLastModified] DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [idx_WFHMJVXRef_RefValue] ON [dbo].[VentureXRef] ([RefValue], [State]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VentureXRef] ADD CONSTRAINT [PK__WFHMJointVenture__28D8CF8F] PRIMARY KEY NONCLUSTERED  ([ID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_WFHMJVXRef_RefValueByType] ON [dbo].[VentureXRef] ([RefValue], [State], [DateDeleted], [RefValueTypeID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VentureXRef] ADD CONSTRAINT [IX_VentureXRef] UNIQUE NONCLUSTERED  ([RefValue], [RefValueTypeID], [State], [DateDeleted]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VentureXRef] ADD CONSTRAINT [fk_WFHMJVXRef_ClientID] FOREIGN KEY ([ClientID]) REFERENCES [dbo].[Client] ([ClientID])
GO
ALTER TABLE [dbo].[VentureXRef] ADD CONSTRAINT [fk_WFHMJVXRef_RefValueTypeID] FOREIGN KEY ([RefValueTypeID]) REFERENCES [dbo].[VentureRefValueType] ([RefValueTypeID])
GO

What is the proper way to do this update statement?

Thanks in advance

Answer 1

YOur problem is you are trying to update it to a value that already exists in the table and so the unique index says it can't.

Answer 2

as mentioned by HILGEm this is a duplicate records problem.To identify records causing duplication you can run below query after substituting your table and database name in place of CTE

use test;
with cte as (
select '123' refvalue union all select '567' union all
select '0000123' union all 
select '123456')

select refvalue from cte as a
where 
len(refvalue) <7 and
exists(
select 1 from cte as b where
len(refvalue)>=7 and
REPLICATE('0',7 - LEN(a.RefValue)) + a.RefValue =b.refvalue
)