Update a column of same table if there is duplicate records
<p>I need to update a column if the rows are duplicate in the table.</p>

<p>Here the scenario is like if there are 3 rows which are duplicate then we have to mark two rows as error and one row as success.</p>

<p>Dummy data could be like</p>

<pre><code>create table test_dup (acc_num number, tel_num number, imsi number, done varchar2(20));

insert into test_dup values (43532111, 9874554422, 58944235, null);
insert into test_dup values (43532111, 9874554422, 58944235, null);
insert into test_dup values (43532111, 9874554422, 58944235, null);

insert into test_dup values (43532333, 9845781554, 265454522, null);

insert into test_dup values (623352777, 9928123457, 89455422, null);
insert into test_dup values (623352777, 9928123457, 89455422, null);

select acc_num, tel_num, imsi 
  from test_dup  
group by acc_num, tel_num, imsi having count(acc_num) &gt; 1;
</code></pre>

<p>This query gives rows which are duplicate
But the question here is that i need to update the DONE column as 'error' for 2 rows of acc_num  43532111 and 1 row as 'success' so for that if i use a update statement like ...</p>

<pre><code>update test_dup
  set done = 'error'
where (acc_num,tel_num, imsi) in (select acc_num, tel_num, imsi
                                 from test_dup
                            group by acc_num, tel_num, imsi
                            having count(acc_num) &gt; 1);
</code></pre>

<p>Then it updates 5 rows i.e. all duplicate rows except non-dups.</p>

<p>but in our case it should only udpate 3 rows 2 duplicate for acc_num = 43532111 and 1 duplicate for acc_num = 623352777 as 'error'</p>

<p>The real table has around 35 columns, so is there any way that we do not need to write each column name for group by clause?</p>

<p>I am using ---</p>

<p>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production</p>

<p>And please do not try to call on above numbers they are only for test purpose!</p>