Forum Stats

  • 3,751,473 Users
  • 2,250,366 Discussions
  • 7,867,435 Comments

Discussions

Update Duplicate values

User_HJS01
User_HJS01 Member Posts: 11 Green Ribbon

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.

Answers

  • EdStevens
    EdStevens Member Posts: 28,394 Gold Crown

    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
    cormaco Member Posts: 1,648 Bronze Crown
    edited May 23, 2021 7:16PM

    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
    User_HJS01 Member Posts: 11 Green Ribbon

    @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
    User_HJS01 Member Posts: 11 Green Ribbon

    @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
    cormaco Member Posts: 1,648 Bronze Crown

    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.