Update Duplicate values

User_HJS01

Hi Everyone,

I need help with the below query;

I have duplicate values in the table.

Primary key value

1 abc

2 abc

3 def

4 def

5 def

Is there anyway that I can assign a character or space to each of these values differently that they do not remain duplicate?

My only objective to update these value (not delete) so that these values remain in the table but are not duplicates any more.

EdStevens

Woulnd't doing so invalidate the data? Wouldn't one suppose that the current value has a specific business meaning? If you pull it off, how do you plan to deal with the fact that 'abc' does not equal 'abc ', when - as far as the business is concerned, they are the same?

Besides, that is a sql question, and this forum is for discussing the client interface program 'SQL Developer'.

cormaco

Here is one way:

drop table ttable;
create table ttable as
    select 1 id, cast('abc' as varchar2(10)) val from dual union all
    select 2 id, 'abc' val from dual union all
    select 3 id, 'def' val from dual union all
    select 4 id, 'def' val from dual union all
    select 5 id, 'def' val from dual union all
    select 6 id, 'ghi' val from dual;
    
merge into ttable
using (select id,row_number() over (partition by val order by id) rn from ttable) ttnew
on (ttable.id = ttnew.id)
when matched then update set val = val || to_char(rn)
where rn > 1;

select * from ttable
order by id;

        ID VAL       
---------- ----------
         1 abc       
         2 abc2      
         3 def       
         4 def2      
         5 def3      
         6 ghi       

I left the first value in every duplicate group unchanged, like this an individual value without duplicates remains the same.

If you don't want this, remove the where rn >1 condition.

User_HJS01

@EdStevens - thank you so much for taking our time to reply to my question.

I am sorry I have not provided you the full context for the reasoning of the above ask.

We are masking the data. The values you see above are masked,

Before masking we have disabled the constraint (the constraint was on the combination of some 5 column).

I am masking one of the the above 5 column called name. The values in this field are duplicate but have space in some of the values hence not being caught as duplicates before masking. Once I have masked the data I have assigned John to Joe but have removed the space in Joe. Hence if Joe is occurring more than once it is duplicate (as my tool have removed the space) while assigning John to Joe.

Now somehow I have to make these values distinct (since these are names, it will not matter to the business as we are not breaking referential integrity) .

That is the reason I need to make these values distinct before enable the constraints back as it is throwing me an error of duplicate values.

User_HJS01

@cormaco - Thank you so much for your help and reply on my question

Unfortunately, i do not have drop, create privilege's.

All I have is update privileges to the existing table where i need to update the existing values in such a way that these values are no more duplicates.

Please refer to my explanation to the reason why I am doing this task in my above comment.

Once again thank you for your time.

cormaco

Unfortunately, i do not have drop, create privilege's.

The drop and create commands in my example only serve to create example data.

They are not part of the solution.