Update duplicate values of the column of a table in oracle

Question

I have a table in which there are duplicate records.

I want this to be like below

Please help me on this.

Answer 1

I am not sure what you are trying to achieve, but I think that you might use a "window SQL" function. This allows you to group rows, for instance to show duplicates in the right order (and potentially eliminate or update them. For instance take a look at the following example where I show the duplicates of the first two dates in your data set ordered by the different values for the third date. I then de-duplicate the data set, keeping only the first value for date3 for each pair of date1, date2.

SQL> connect scott/tiger@tiger
Connected.
SQL> set echo on
SQL> alter session set nls_date_format='DD-MON-RR';

Session altered.

SQL> drop table test;

Table TEST dropped.

SQL> create table test (
  2     col1    number,
  3     d1      date,
  4     d2      date,
  5     d3      date);

Table TEST created.

SQL> insert into test values (1,'01-mar-21','03-sep-21','21-oct-21');

1 row inserted.

SQL> insert into test values (1,'01-mar-21','21-oct-21','21-oct-21');

1 row inserted.

SQL> insert into test values (1,'01-mar-21','21-oct-21','21-oct-21');

1 row inserted.

SQL> insert into test values (1,'01-mar-21','21-oct-21','22-oct-21');

1 row inserted.

SQL> insert into test values (1,'01-mar-21','22-oct-21','22-oct-21');

1 row inserted.

SQL> commit;

Commit complete.

SQL> -- show duplicates on d1, d2 order by d3
SQL> select row_number()
  2  over (partition by d1, d2 order by d3) rn, t.d1, t.d2, t.d3
  3  from test t;

   RN           D1           D2           D3
_____ ____________ ____________ ____________
    1 01-MAR-21    03-SEP-21    21-OCT-21
    1 01-MAR-21    21-OCT-21    21-OCT-21
    2 01-MAR-21    21-OCT-21    21-OCT-21
    3 01-MAR-21    21-OCT-21    22-OCT-21
    1 01-MAR-21    22-OCT-21    22-OCT-21

Notice that rn is incremented for each d3 within a given pair of d1,d2

SQL> -- eliminate the duplicates on d1, d2 (keep first one)
SQL> select x.* from (select row_number() over (partition by d1, d2 order by d3) rn, t.d1, t.d2, t.d3
  2  from test t )x where x.rn=1;

   RN           D1           D2           D3
_____ ____________ ____________ ____________
    1 01-MAR-21    03-SEP-21    21-OCT-21
    1 01-MAR-21    21-OCT-21    21-OCT-21
    1 01-MAR-21    22-OCT-21    22-OCT-21