Home » SQL & PL/SQL » SQL & PL/SQL » duplicate records
duplicate records [message #292661] Wed, 09 January 2008 09:41 Go to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Hi,
I want to delete duplicate records from table .
this table holds primary key value for other three table.
now I want to delete only those records which are not primary key for other table.

I thought this query will work but it's returning all duplicate records.
SELECT cust_id
  FROM (SELECT   cust_id, primary_ind, COUNT (*)
            FROM customercontacts
           WHERE primary_ind = 'Y'
             AND (   ID NOT IN (SELECT ccnt_id
                                  FROM custconroles)
                  OR ID NOT IN (SELECT ccnt_id
                                  FROM jobcustcont)
                  OR ID NOT IN (SELECT ccnt_id_contact
                                  FROM salord)
                 )
          HAVING COUNT (*) > 1
        GROUP BY cust_id, primary_ind)

Please suggest...
ASHish...
Re: duplicate records [message #292662 is a reply to message #292661] Wed, 09 January 2008 09:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have a look at this and build your query.

By
Vamsi
Re: duplicate records [message #292666 is a reply to message #292662] Wed, 09 January 2008 09:51 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Thanks Vamsi for your replay....
But i am looking for logic which will delete records which will eliminate those rows which are primary key for other records in different table
something to replace with this part of query

AND (   ID NOT IN (SELECT ccnt_id
                                  FROM custconroles)
                  OR ID NOT IN (SELECT ccnt_id
                                  FROM jobcustcont)
                  OR ID NOT IN (SELECT ccnt_id_contact
                                  FROM salord)
                 )

Please suggest...
ASHish...
Re: duplicate records [message #292669 is a reply to message #292666] Wed, 09 January 2008 09:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have you had a look at the link.
Can't you add your filters in the inner select?
If not, why?

Provide your sample data and test case.

By
Vamsi

[Updated on: Wed, 09 January 2008 10:05]

Report message to a moderator

Re: duplicate records [message #292670 is a reply to message #292669] Wed, 09 January 2008 10:06 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

You are very fast... Smile
I had look at the link but not able to find out solution....

what do you mean by
Quote:

Can't you add your filters in the inner select?


give me some time i will provide you sample data..

ASHish...
Re: duplicate records [message #292681 is a reply to message #292670] Wed, 09 January 2008 10:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Add
AND primary_ind = 'Y'
             AND (   ID NOT IN (SELECT ccnt_id
                                  FROM custconroles)
                  OR ID NOT IN (SELECT ccnt_id
                                  FROM jobcustcont)
                  OR ID NOT IN (SELECT ccnt_id_contact
                                  FROM salord)
                 )
also in the delete duplicate sql.

By
Vamsi
Re: duplicate records [message #292936 is a reply to message #292681] Thu, 10 January 2008 03:44 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

sample data in attached file.
i added the where clause in both query inner and outer but not getting desired out put... plz help...

I want to delete record from table1 which are duplicate (dup_col1 and dup_col2) and not present in table2 and table3 (Ref. Key ID)

suggest something....
ASHish....
  • Attachment: test.xls
    (Size: 16.00KB, Downloaded 304 times)
Re: duplicate records [message #292963 is a reply to message #292936] Thu, 10 January 2008 05:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, I (and many others here) can or will not download XLS, DOC, ... or any other possible harmful file. Perhaps you can post a .txt file instead?

MHE
Re: duplicate records [message #292968 is a reply to message #292936] Thu, 10 January 2008 05:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't see any related code to
SQL> DELETE FROM table_name A WHERE ROWID > (
  2    SELECT min(rowid) FROM table_name B
  3    WHERE A.key_values = B.key_values);
in the xls

By
Vamsi
Re: duplicate records [message #292978 is a reply to message #292968] Thu, 10 January 2008 06:25 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

This is the query I have written in the XLS...

Quote:

SELECT ID
FROM (SELECT ID, dup_col1, dup_col2, COUNT (*)
FROM table1
WHERE dup_col2 = 'Y'
AND ( table1.ID NOT IN (SELECT table1_id
FROM table2)
OR table1.ID NOT IN (SELECT table1_id
FROM table3)
)
HAVING COUNT (*) > 1
GROUP BY dup_id1, dup_id12)




I am using following query to delete the duplicate records
SELECT ID
FROM (SELECT ID, dup_col1, dup_col2, COUNT (*)
FROM table1
WHERE dup_col2 = 'Y'
HAVING COUNT (*) > 1
GROUP BY dup_id1, dup_id12)[/QUOTE]

and this is working fine but i am not able to write query for this condition which will eliminate those records which are primary key for other table.


something is wrong wtih this part of query
Quote:

AND ( table1.ID NOT IN (SELECT table1_id
FROM table2)
OR table1.ID NOT IN (SELECT table1_id
FROM table3)
)


AShish....
Re: duplicate records [message #292980 is a reply to message #292978] Thu, 10 January 2008 06:29 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Test data in .txt format

I want to delete record from table1 which are duplicate (dup_col1 and dup_col2) and not present in table2 and table3 (Ref. Key ID)

table1 (combination of duplicate column 1 and duplicate column 2 is duplicate )
ID Dup_col1 Dup_col2
1 111 Y
2 111 Y
3 222 Y
4 222 Y
5 333 Y
6 333 Y
7 aaa Y
8 aaa Y
9 bbb Y
10 bbb Y
11 ccc Y
12 ccc Y
13 11aa Y
14 11aa Y
15 22bb Y
16 22bb Y
17 xx11 Y
18 xx11
19 xx33 Y
20 xx44 Y

table2
ID table1_ID
TAB_b_1 1
TAB_b_2 3
TAB_B_3 5
TAB_b_4 14
TAB_b_5 15
TAB_B_6 20

Table3
ID table1_ID
TAB_C_1 7
TAB_C_2 9
TAB_C_3 11
TAB_C_4 13
TAB_C_5 16
TAB_C_6 19


query below returning all duplicate rows from table1
SELECT ID
FROM (SELECT ID, dup_col1, dup_col2, COUNT (*)
FROM table1
WHERE dup_col2 = 'Y'
AND ( table1.ID NOT IN (SELECT table1_id
FROM table2)
OR table1.ID NOT IN (SELECT table1_id
FROM table3)
)
HAVING COUNT (*) > 1
GROUP BY dup_id1, dup_id12)

ASHish.....
Re: duplicate records [message #293010 is a reply to message #292980] Thu, 10 January 2008 10:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems you have misread my posts.
I mean you have to use rowid in your query, as mentioned in the link.

By
Vamsi
Re: duplicate records [message #293017 is a reply to message #293010] Thu, 10 January 2008 10:48 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Vamsi Thanks for your reply once again.... but I think u are not understanding my problem.
my problem is not about fetching duplicate records. I am fetching duplicate record with my query and its fast too. but while fetching duplicate recodes I don't want to fetch all duplicate records. there is a primary key column in my table which is holds reference to other tables so i want to element those records and only need to fetch those recodes which are not present in other table.

In the given test Data from table1 I need to delete (ONLY)these
ID's 2,4,6,8,10,12.


Please suggest.... Smile
ASHish...
Re: duplicate records [message #293026 is a reply to message #293017] Thu, 10 January 2008 11:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try replacing the "OR" with "And" in your not in clause, see if that helps.

Alternatively, try to use union something like
select * from table_a
where <column> not in (select val from table_b
                       union
                       select val from table_c
                       ...)

HTH

Regards

Raj

[Updated on: Thu, 10 January 2008 11:32]

Report message to a moderator

Re: duplicate records [message #293028 is a reply to message #293017] Thu, 10 January 2008 11:36 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
So, you don't have same ID for any two rows.
1 111 Y
2 111 Y

Then you can use the above.

By
Vamsi
Re: duplicate records [message #293138 is a reply to message #292661] Fri, 11 January 2008 00:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I think what Ashish trying to convey is something diffrerent . If I am not wring , he is trying to achieve something like ,

SQL> SELECT ID, DUP_COL1, DUP_COL2 FROM TABLE1;

ID         DUP_C D
---------- ----- -
1          11    Y
2          11    Y
3          22    Y
4          22    Y
5          33    Y
6          33    Y
7          44    Y
8          44    Y
9          55    Y
10         55    Y
11         66    Y
12         66    Y
13         77    Y
14         77    Y
15         88    Y
16         88    Y
17         99    Y
18         99
19         00    Y
20         00    Y

20 rows selected.

SQL> SELECT * from (
  2   select ID , DUP_COL1,DUP_COL2 ,
  3             (select count(*) from table1 t1
  4                                              WHERE t1.DUP_COL1 = T.DUP_COL1
  5                                              AND   NVL(T1.DUP_COL2,'X') = NV
L(T.DUP_COL2,'X') ) CNT
  6  from table1 T
  7  where ID not in (select TABLE1_ID from table2
  8                         union
  9                         select TABLE1_ID from table3))
 10  WHERE CNT >1;

ID         DUP_C D        CNT
---------- ----- - ----------
2          11    Y          2
4          22    Y          2
6          33    Y          2
8          44    Y          2
10         55    Y          2
12         66    Y          2

6 rows selected.

SQL>


Thumbs Up
Rajuvan.

[Updated on: Fri, 11 January 2008 01:27]

Report message to a moderator

Re: duplicate records [message #293233 is a reply to message #293138] Fri, 11 January 2008 04:53 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Thanks lot guys.... but I think there is something missing from my side thats why you are not able to provide proper query.....
I will try to make it more simple using PL/sql script. Hope this will work....

BEGIN
   FOR i IN
      (SELECT *
         FROM customer_contacts t1
        WHERE EXISTS (
                 SELECT 'x'
                   FROM customer_contacts t2
                  WHERE t2.cust_id = t1.cust_id
                    AND t2.primary_ind = t1.primary_ind
                    AND t2.ROWID < t1.ROWID
                    AND (   t2.ID NOT IN (SELECT ccnt_id
                                            FROM customer_contact_roles)
                         OR t2.ID NOT IN (SELECT ccnt_id
                                            FROM job_customer_contacts)
                         OR t2.ID NOT IN (SELECT ccnt_id_contact
                                            FROM sales_orders)
                        )
              )
          AND t1.primary_ind = 'Y'
          AND (   t1.ID NOT IN (SELECT ccnt_id
                                  FROM customer_contact_roles)
               OR t1.ID NOT IN (SELECT ccnt_id
                                  FROM job_customer_contacts)
               OR t1.ID NOT IN (SELECT ccnt_id_contact
                                  FROM sales_orders)
              ))
   LOOP
      BEGIN
         DELETE FROM customer_contacts
               WHERE ID = i.ID;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (sqlerr || i.ID);
      END;
   END LOOP;
END;


when I run this script I get following errors
Quote:

ORA-02292: integrity constraint (PULSE_OWNER.JCC_CCNT_FK) violated - child record found620563
ORA-02292: integrity constraint (PULSE_OWNER.SORD_CCNT_CONTACT_FK) violated - child record found626080
ORA-02292: integrity constraint (PULSE_OWNER.JCC_CCNT_FK) violated - child record found616600
ORA-02292: integrity constraint (PULSE_OWNER.SORD_CCNT_CONTACT_FK) violated - child record found628083
ORA-02292: integrity constraint (PULSE_OWNER.SORD_CCNT_CONTACT_FK) violated - child record found643566


I know these is because the records I am trying to delete are
parent records but I have written flowing clause in my query which thought will element these parent records.
          AND (   t1.ID NOT IN (SELECT ccnt_id
                                  FROM customer_contact_roles)
               OR t1.ID NOT IN (SELECT ccnt_id
                                  FROM job_customer_contacts)
               OR t1.ID NOT IN (SELECT ccnt_id_contact
                                  FROM sales_orders)
              )


but as you can see this not not working.
and now I want to know what should replace in the query which will give me only those rows which are not parent record.

please dont tell me use pl/sql scrip I want to write pure SQL statement for this.

ASHish.....
Re: duplicate records [message #294371 is a reply to message #293028] Thu, 17 January 2008 06:23 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

any clues please.....
Re: duplicate records [message #294383 is a reply to message #292661] Thu, 17 January 2008 07:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We dont know you table and Db strructure . It might be having some other integrity constraints.

Thumbs Up
Rajuvan.
Re: duplicate records [message #294389 is a reply to message #294383] Thu, 17 January 2008 07:36 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

ya there is and i want to avoid those rows
Re: duplicate records [message #294423 is a reply to message #294389] Thu, 17 January 2008 09:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Change the ORs to ANDs in the not exists
Previous Topic: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH
Next Topic: Sequence to start with a value known dynamically
Goto Forum:
  


Current Time: Wed Dec 07 15:01:24 CST 2016

Total time taken to generate the page: 0.16874 seconds