Hi,
I need to update a column if the rows are duplicate in the table.
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.
Dummy data could be like
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) > 1;
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 ...
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) > 1);
Then it updates 5 rows i.e. all duplicate rows except non-dups.
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'
ALSO 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 !
I am using ---
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
And Please do not try to call on above numbers they are only for test purpose !!