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.
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
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.