I have to change a database to include a distinctive constraint on the table column, however the VARCHAR data in it's not unique.

How do i update individuals duplicate records to ensure that each value is exclusive with the addition of a consecutive number in the finish from the existing data?

e.g. I must change 'name' to 'name1', 'name2', 'name3'

Listed here are 2 good examples with while using MS SQL SERVER flavor of sql.

Setup Example:

create table test (id int identity primary key, val varchar(20) )
	--id is a pk for the cursor so it can update using "where current of"

-- name a is not duplicated
-- name b is duplicated 3 times
-- name c is duplicated 2 times

insert test values('name a')
insert test values('name b')
insert test values('name c')
insert test values('name b')
insert test values('name b')
insert test values('name c')

Sql 20052008: ( Calculated Table Expression )

begin tran; -- Computed table expressions require the statement prior to end with ;

with cte(val,row) as (

	select val, row_number() over (partition by val order by val) row
	--partiton is important. it resets the row_number on a new val
	from test 
	where val in ( -- only return values that are duplicated
		select val
		from test
		group by val
		having count(val)>1
	)
)
update cte set val = val + ltrim(str(row))
--ltrim(str(row)) = converting the int to a string and removing the padding from the str command.

select * from test

rollback

Sql 2000: (Cursor example)

begin tran

declare @row int, @last varchar(20), @current varchar(20)
set @last = ''
declare dupes cursor
	for
	select val 
	from test 
	where val in ( -- only return values that are duplicated
		select val
		from test
		group by val
		having count(val)>1
	)
	order by val

	for update of val

open dupes
fetch next from dupes into @current
while @@fetch_status = 0
begin
	--new set of dupes, like the partition by in the 2005 example
	if @last != @current
		set @row = 1

	update test
		--@last is being set during the update statement
		set val = val + ltrim(str(@row)), @last = val
		where current of dupes

	set @row = @row + 1

	fetch next from dupes into @current
end
close dupes
deallocate dupes

select * from test

rollback

I folded back each one of the updates because my script file consists of both good examples. This permitted me to check the functionality without resetting the rows up for grabs.

What database are you currently using?

In Oracle there's a:

NOVALIDATE Validates changes but doesn't validate data formerly existing within the table

Example:

ALTER TABLE <table_name> ENABLE NOVALIDATE UNIQUE;

If you're not using Oracle then look into the SQL reference for the particular database.

You could include another column into it... like

update mytable set mycolumn = concat(mycolumn, id) 
            where id in (<select duplicate records>);

replace id with whatever column makes mycolumn unique

Open a cursor up for grabs, purchased with that column. Have a previous value variable, initialized to null, as well as an index variable initialized to . When the current value = the prior value, increment the index and append the index towards the area value. when the current value <> the prior value, totally reset the index to and the area value out of the box. Set the prior value variable = the present value. Move onto the following row and repeat.