This discussion is archived
8 Replies Latest reply: Jan 27, 2013 3:37 AM by Solomon Yakobson RSS

Deleting Duplicate records ::(Please reply to it ::)

983563 Newbie
Currently Being Moderated
Hi experts i am new to the oracle .Please me with this delete query .

When ever the mobileno in TEST_REG,TEST_MUL matches then it has to check for accountno gets duplicated in TEST_MUL table .
If duplicate accounts are there for that particular mobileno
Then it has to delete that duplicate accountno whose ids are not equal in both the
tables (those accountno records should get deleted from test_mul table)

             
Script For the query :
---------------------

create table test_mul (mobileno VARCHAR2 (20 Byte),accountno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),v_date date);

create table test_reg (mobileno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),r_date date,mas varchar2(1) default 'Y');



alter table test_mul modify v_date default sysdate;

alter table test_reg modify r_date default sysdate;


Insertion of records into test_reg table :


insert into test_reg (mobileno,id) values ('+227299001081','AAA');
insert into test_reg (mobileno,id) values ('+911000000001','BBB');
insert into test_reg (mobileno,id) values ('+911000000002','CCC');
insert into test_reg (mobileno,id) values ('+911000000005','DDD');
insert into test_reg (mobileno,id) values ('+911000000006','EEE');
insert into test_reg (mobileno,id) values ('+911000000007','FFF');

commit;



==================================================================


INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','AAA');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','123');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','43443345432344','AAA');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','43443345432344','AAA');
------------------------------

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000001','08490100005169','BBB');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000001','08490100006867','BBB');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000001','08490100009602','BBB');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000001','08490100009602','456');

---------------------------------


INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000005','47775524511505','DDD');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000002','47775521111505','DDD');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000002','47775524511505','789');

-------------------------------



INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000006','47775545611506','EEE');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000006','47775521111506','EEE');

--------------------------------


INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000007','47987521111507','FFF');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000007','47775521111507','FFF');

INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+911000000007','47775521111507','987');

commit;
Edited by: 980560 on Jan 26, 2013 7:56 PM
  • 1. Re: Deleting Duplicate records ::
    sb92075 Guru
    Currently Being Moderated
  • 2. Re: Deleting Duplicate records ::
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.

    Always post the results you want from the given data. I'm sorry, I can't even tell which table(s) you want to delete from. Post what you want the tables to contain after all the deleting is over. Explain why you want those results. For example "In table ... row ... is deleted because ... but row ... is not deleted, because ...".

    It looks like you posted 3 copies of this question. That's probably not your fault; this site can be flaky that way. Even if it's not your fault, please fix it. Mark the other threads, {message:id=10816154} and {message:id=10816155}, as "Answered" right away; that will save time for other people and for you, since you'll only have to look in one place for answers.
  • 3. Re: Deleting Duplicate records ::(Please reply to it ::)
    ranit B Expert
    Currently Being Moderated
    Something like this?

    Go through the complete workout :
    Ranit>> select *
      2  from
      3       test_mul;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE                                                                                                                                                               
    -------------------- -------------------- ------------ ---------                                                                                                                                                            
    +227299001081        37775521122561       AAA          26-JAN-13                                                                                                                                                            
    +227299001081        37775521122561       123          26-JAN-13                                                                                                                                                            
    +227299001081        43443345432344       AAA          26-JAN-13                                                                                                                                                            
    +227299001081        43443345432344       AAA          26-JAN-13                                                                                                                                                            
    +911000000001        08490100005169       BBB          26-JAN-13                                                                                                                                                            
    +911000000001        08490100006867       BBB          26-JAN-13                                                                                                                                                            
    +911000000001        08490100009602       BBB          26-JAN-13                                                                                                                                                            
    +911000000001        08490100009602       456          26-JAN-13                                                                                                                                                            
    +911000000005        47775524511505       DDD          26-JAN-13                                                                                                                                                            
    +911000000002        47775521111505       DDD          26-JAN-13                                                                                                                                                            
    +911000000002        47775524511505       789          26-JAN-13                                                                                                                                                            
    +911000000006        47775545611506       EEE          26-JAN-13                                                                                                                                                            
    +911000000006        47775521111506       EEE          26-JAN-13                                                                                                                                                            
    +911000000007        47987521111507       FFF          26-JAN-13                                                                                                                                                            
    +911000000007        47775521111507       FFF          26-JAN-13                                                                                                                                                            
    +911000000007        47775521111507       987          26-JAN-13                                                                                                                                                            
    
    16 rows selected.
    
    Ranit>> select *
      2  from
      3       test_reg;
    
    MOBILENO             ID           R_DATE    M                                                                                                                                                                               
    -------------------- ------------ --------- -                                                                                                                                                                               
    +227299001081        AAA          26-JAN-13 Y                                                                                                                                                                               
    +911000000001        BBB          26-JAN-13 Y                                                                                                                                                                               
    +911000000002        CCC          26-JAN-13 Y                                                                                                                                                                               
    +911000000005        DDD          26-JAN-13 Y                                                                                                                                                                               
    +911000000006        EEE          26-JAN-13 Y                                                                                                                                                                               
    +911000000007        FFF          26-JAN-13 Y                                                                                                                                                                               
    
    6 rows selected.
    
    Ranit>> select m.*
      2  from
      3       test_reg r, test_mul m
      4  where
      5       r.mobileno = m.mobileno
      6  and
      7       r.id != m.id;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE                                                                                                                                                               
    -------------------- -------------------- ------------ ---------                                                                                                                                                            
    +227299001081        37775521122561       123          26-JAN-13                                                                                                                                                            
    +911000000001        08490100009602       456          26-JAN-13                                                                                                                                                            
    +911000000002        47775521111505       DDD          26-JAN-13                                                                                                                                                            
    +911000000002        47775524511505       789          26-JAN-13                                                                                                                                                            
    +911000000007        47775521111507       987          26-JAN-13                                                                                                                                                            
    
    Ranit>> delete from test_mul m
      2  where (mobileno,id) != (
      3  select r.mobileno,r.id
      4  from
      5       test_reg r
      6  where
      7       r.mobileno = m.mobileno
      8  and
      9       r.id != m.id
     10  );
    
    5 rows deleted.
    
    Ranit>> select *
      2  from
      3  test_mul;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE                                                                                                                                                               
    -------------------- -------------------- ------------ ---------                                                                                                                                                            
    +227299001081        37775521122561       AAA          26-JAN-13                                                                                                                                                            
    +227299001081        43443345432344       AAA          26-JAN-13                                                                                                                                                            
    +227299001081        43443345432344       AAA          26-JAN-13                                                                                                                                                            
    +911000000001        08490100005169       BBB          26-JAN-13                                                                                                                                                            
    +911000000001        08490100006867       BBB          26-JAN-13                                                                                                                                                            
    +911000000001        08490100009602       BBB          26-JAN-13                                                                                                                                                            
    +911000000005        47775524511505       DDD          26-JAN-13                                                                                                                                                            
    +911000000006        47775545611506       EEE          26-JAN-13                                                                                                                                                            
    +911000000006        47775521111506       EEE          26-JAN-13                                                                                                                                                            
    +911000000007        47987521111507       FFF          26-JAN-13                                                                                                                                                            
    +911000000007        47775521111507       FFF          26-JAN-13                                                                                                                                                            
    
    11 rows selected.
    Please let us know if this helps and meets your requirement.
  • 4. Re: Deleting Duplicate records ::(Please reply to it ::)
    983563 Newbie
    Currently Being Moderated
     
     Thanks Ranit it is really help ful this is the another case where ids are equal (Your previous query is right)
     
    When ever the mobileno in TEST_REG,TEST_MUL matches then it has to check for accountno gets duplicated in TEST_MUL table .
    If duplicate accounts are there for that particular mobileno
    Then it has to delete that duplicate accountno whose ids are  equal in both the
    tables (those duplicate accountno records should get deleted from test_mul table)
     
    
    create table test_mul (mobileno VARCHAR2 (20 Byte),accountno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),v_date date);
     
    create table test_reg (mobileno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),r_date date,mas varchar2(1) default 'Y');
    
     
    insert into test_reg (mobileno,id) values ('+227299001081','AAA');
    
     
     
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','AAA');
     
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','AAA');
    
    SQL> select * from test_reg;
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA                    Y
    
    SQL> select * from test_mul;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13  -- I WANT TO DELETE THIS RECORD 
  • 5. Re: Deleting Duplicate records ::(Please reply to it ::)
    Solomon Yakobson Guru
    Currently Being Moderated
    980560 wrote:
    When ever the mobileno in TEST_REG,TEST_MUL matches then it has to check for accountno gets duplicated in TEST_MUL table .
    If duplicate accounts are there for that particular mobileno
    Then it has to delete that duplicate accountno whose ids are equal in both the
    tables (those duplicate accountno records should get deleted from test_mul table)
    SQL> select * from test_reg order by mobileno,id;
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA          27-JAN-13 Y
    +227299001081        AAA          27-JAN-13 Y
    +911000000001        BBB          27-JAN-13 Y
    +911000000002        CCC          27-JAN-13 Y
    +911000000005        DDD          27-JAN-13 Y
    +911000000006        EEE          27-JAN-13 Y
    +911000000007        FFF          27-JAN-13 Y
    
    7 rows selected.
    
    SQL> select * from test_mul order by mobileno,id;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       123          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +911000000001        08490100009602       456          27-JAN-13
    +911000000001        08490100006867       BBB          27-JAN-13
    +911000000001        08490100005169       BBB          27-JAN-13
    +911000000001        08490100009602       BBB          27-JAN-13
    +911000000002        47775524511505       789          27-JAN-13
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +911000000002        47775521111505       DDD          27-JAN-13
    +911000000005        47775524511505       DDD          27-JAN-13
    +911000000006        47775521111506       EEE          27-JAN-13
    +911000000006        47775545611506       EEE          27-JAN-13
    +911000000007        47775521111507       987          27-JAN-13
    +911000000007        47987521111507       FFF          27-JAN-13
    +911000000007        47775521111507       FFF          27-JAN-13
    
    18 rows selected.
    Now:
    delete test_mul
     where (
            rowid,
            1
           ) in (
                 select  b.rowid rid,
                         row_number() over(partition by b.mobileno,b.accountno,b.id order by 1) rn
                   from  test_reg a,
                         test_mul b
                   where b.mobileno = a.mobileno
                     and b.id = a.id
                )
    /
    Results:
    SQL> select * from test_mul order by mobileno,id;
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       123          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +911000000001        08490100009602       456          27-JAN-13
    +911000000002        47775524511505       789          27-JAN-13
    +911000000002        47775521111505       DDD          27-JAN-13
    +911000000007        47775521111507       987          27-JAN-13
    
    8 rows selected.
    
    SQL>
    SY.
  • 6. Re: Deleting Duplicate records ::(Please reply to it ::)
    983563 Newbie
    Currently Being Moderated
     
     
     Sry i want like this :
     
     
    create table test_mul (mobileno VARCHAR2 (20 Byte),accountno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),v_date date);
     
    create table test_reg (mobileno VARCHAR2 (20 Byte),id VARCHAR2 (12 Byte),r_date date,mas varchar2(1) default 'Y');
     
     
     
     insert into test_reg (mobileno,id) values ('+227299001081','AAA');
    
     
    commit;
     
     
     
    ==================================================================
     
     
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','AAA');
     
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','37775521122561','AAA'); *I WANT TO DELETE THIS RECOED (SICE MOBILENOS ARE EQUAL IN BOTH TABLES AND for that mobileno  ACCOUNT NOS AND IDS ARE ALSO EQUAL(DUPLICATED) in TEST_MUL table )* 
    
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','43443345432344','AAA');
     
    INSERT INTO TEST_MUL(mobileno,accountno,id) VALUES ('+227299001081','43443345432344','AAA'); *I WANT TO DELETE THIS RECOED (SICE MOBILENOS ARE EQUAL IN BOTH TABLES AND for that mobileno  ACCOUNT NOS AND IDS ARE ALSO EQUAL(DUPLICATED) in TEST_MUL table )*
  • 7. Re: Deleting Duplicate records ::(Please reply to it ::)
    Solomon Yakobson Guru
    Currently Being Moderated
    And this is EXACTLY what I gave you:
    SQL> select  *
      2    from  test_reg
      3  /
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA          27-JAN-13 Y
    
    SQL> select  *
      2    from  test_mul
      3  /
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    
    SQL> delete test_mul
      2   where (
      3          rowid,
      4          1
      5         ) in (
      6               select  b.rowid rid,
      7                       row_number() over(partition by b.mobileno,b.accountno,b.id order by 1) rn
      8                 from  test_reg a,
      9                       test_mul b
     10                 where b.mobileno = a.mobileno
     11                   and b.id = a.id
     12              )
     13  /
    
    2 rows deleted.
    
    SQL> select  *
      2    from  test_reg
      3  /
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA          27-JAN-13 Y
    
    SQL> select  *
      2    from  test_mul
      3  /
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    
    SQL>
    SY.
  • 8. Re: Deleting Duplicate records ::(Please reply to it ::)
    Solomon Yakobson Guru
    Currently Being Moderated
    Question is what you want to do when:
    SQL> select  *
      2    from  test_reg
      3  /
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA          27-JAN-13 Y
    +227299001081        BBB          27-JAN-13 Y
    
    SQL> select  *
      2    from  test_mul
      3  /
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       BBB          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        43443345432344       BBB          27-JAN-13
    
    SQL>
    Or when:
    SQL> select  *
      2    from  test_reg
      3  /
    
    MOBILENO             ID           R_DATE    M
    -------------------- ------------ --------- -
    +227299001081        AAA          27-JAN-13 Y
    
    SQL> select  *
      2    from  test_mul
      3  /
    
    MOBILENO             ACCOUNTNO            ID           V_DATE
    -------------------- -------------------- ------------ ---------
    +227299001081        37775521122561       AAA          27-JAN-13
    +227299001081        37775521122561       BBB          27-JAN-13
    +227299001081        43443345432344       AAA          27-JAN-13
    +227299001081        43443345432344       BBB          27-JAN-13
    
    SQL>
    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points