Home » SQL & PL/SQL » SQL & PL/SQL » Wanted to delete duplicate records (Oracle 11 g)
Wanted to delete duplicate records [message #665128] Tue, 22 August 2017 19:00 Go to next message
samiran_cts
Messages: 46
Registered: January 2012
Member
Hi Sir,

I need to delete duplicate inactive records in a particular date where there is active and inactive records coming based on id. But I will not take any action if in a particular date , that id has all active records or all inactive records.

create table t2
(id number,
valid_flg char(1),
del_in char(1),
status char(1),
ins_dt date);

insert into t2 values (100,'N','N','I',sysdate);
insert into t2 values (100,'Y','Y','A',sysdate);
insert into t2 values (100,'Y','N','A',sysdate);
insert into t2 values (100,'N','Y','I',sysdate);
insert into t2 values (200,'N','Y','I',sysdate);
insert into t2 values (200,'Y','N','I',sysdate);


The record is invalid if valid_flg ='N' or del_in ='Y' or status ='I'

I have used this code to delete duplicate but it is deleting all inactive records for a particular date which has only inactive records.I am trying to fetch duplicate records based on this condition but it is not working. So it is deleting record 200 but it should not.
select id , count(*) from t2
    where  valid_flg ='Y' or del_in ='N' or status ='A'
    group by id;

I want to keep record:

100,'Y','N','A',sysdate
200,'N','Y','I',sysdate
200,'Y','N','I',sysdate

Please help sir.

Regards,
Samiran

[Updated on: Tue, 22 August 2017 19:22]

Report message to a moderator

Re: Wanted to delete duplicate records [message #665132 is a reply to message #665128] Tue, 22 August 2017 23:05 Go to previous messageGo to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

SELECT t2.rowid rid, t2.*,
  rank() over (partition BY id order by
  CASE
    WHEN status = 'A'
    THEN 0
    ELSE 1
  END,
  CASE
    WHEN status = 'I'
    THEN 0
    ELSE
      CASE
        WHEN valid_flg = 'Y'
        THEN 0
        ELSE 1
      END
  END,
  CASE
    WHEN status = 'I'
    THEN 0
    ELSE
      CASE
        WHEN del_in = 'N'
        THEN 0
        ELSE 1
      END
  END) rnk
FROM t2;

Try above, if it's really big resultset, put the above query in cursor, along with attached rowid, delete all the records on the basis of rowid where "rnk>1"

Regards,
Manu
Re: Wanted to delete duplicate records [message #665140 is a reply to message #665132] Wed, 23 August 2017 04:35 Go to previous messageGo to next message
quirks
Messages: 60
Registered: October 2014
Member
How about that:
WITH
    T2
    AS
        (SELECT 100 ID, 'N' VALID_FLG, 'N' DEL_IN, 'I' STATUS, TRUNC(SYSDATE) INS_DT FROM DUAL
         UNION ALL
         SELECT 100 ,'N' ,'N' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 100 ,'Y' ,'N' ,'A' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 100 ,'N' ,'Y' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 200 ,'N' ,'Y' ,'I' ,TRUNC(SYSDATE) FROM DUAL
         UNION ALL
         SELECT 200 ,'Y' ,'N' ,'I' ,TRUNC(SYSDATE) FROM DUAL),
    WITH_FILTER
    AS
        (SELECT ID
               ,VALID_FLG
               ,DEL_IN
               ,STATUS
               ,INS_DT
               ,CASE
                    WHEN count(distinct STATUS) OVER(PARTITION BY ID ) = 1
                    THEN
                        'Y'
                    ELSE
                        VALID_FLG
                END
                    AS KEEP_ME
           FROM T2)
SELECT *
  FROM WITH_FILTER
 WHERE KEEP_ME = 'Y'

[Updated on: Wed, 23 August 2017 04:37]

Report message to a moderator

Re: Wanted to delete duplicate records [message #665142 is a reply to message #665140] Wed, 23 August 2017 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 12956
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't appear to have read the OPs definition of invalid very carefully
Re: Wanted to delete duplicate records [message #665143 is a reply to message #665142] Wed, 23 August 2017 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 12956
Registered: September 2008
Location: Rainy Manchester
Senior Member
Here's an approach with aggregates:
DELETE FROM t2 
WHERE id IN (SELECT id 
             FROM t2
             GROUP BY id
             HAVING COUNT(CASE WHEN valid_flg = 'N' 
                               OR del_in = 'Y' 
                               OR status = 'I' THEN 1 END) < COUNT(*)
            )
AND (valid_flg = 'N' 
     OR del_in = 'Y' 
     OR status = 'I');
Re: Wanted to delete duplicate records [message #665144 is a reply to message #665132] Wed, 23 August 2017 05:36 Go to previous messageGo to next message
cookiemonster
Messages: 12956
Registered: September 2008
Location: Rainy Manchester
Senior Member
The rank approach can be simplified:
SELECT t2.rowid rid, t2.*,
  rank() over (partition BY id order by
  CASE
    WHEN valid_flg ='N' OR del_in ='Y' OR status ='I'
    THEN 1
    ELSE 0
  END) rnk,
FROM t2;

Turning it into a delete:
delete from t2 
where t2.rowid in (select rid
                   from (SELECT inner.rowid rid,
                                rank() over (partition BY id 
                                             order by CASE WHEN valid_flg ='N' 
                                                           OR del_in ='Y' 
                                                           OR status ='I'
                                                           THEN 1
                                                           ELSE 0
                                                       END
                                            ) rnk,
                         FROM t2 inner
                        )
                    where rnk > 1
                   );
Re: Wanted to delete duplicate records [message #665166 is a reply to message #665144] Wed, 23 August 2017 23:17 Go to previous messageGo to next message
samiran_cts
Messages: 46
Registered: January 2012
Member
Thank all of you for the valuables inputs which I will consider. I have also tried in this manner to get the records need to be deleted.
  select * from t1 tab1 where trunc(instance ) = '23-Aug-17'
  and (valid_flg = 'N' or (ststus ='I' or del_in='Y'))
  and id in ( select id from t1 tab2 where trunc(instance ) = '23-Aug-17'
  and (valid_flg ='Y' and (ststus ='A' and del_in ='N')));

Regards,
Samiran
Re: Wanted to delete duplicate records [message #665177 is a reply to message #665166] Thu, 24 August 2017 07:48 Go to previous messageGo to next message
joy_division
Messages: 4800
Registered: February 2005
Location: East Coast USA
Senior Member
samiran_cts wrote on Thu, 24 August 2017 00:17

  select * from t1 tab1 where trunc(instance ) = '23-Aug-17'
..
  and id in ( select id from t1 tab2 where trunc(instance ) = '23-Aug-17'
Comparing a date to a string is poor coding and is just a matter of time before it stops working.
Re: Wanted to delete duplicate records [message #665184 is a reply to message #665177] Thu, 24 August 2017 10:07 Go to previous message
samiran_cts
Messages: 46
Registered: January 2012
Member
Ok Sir, I will try to implement the query as discussed in the forum.
Previous Topic: Applying function on column having multiple datatypes
Next Topic: Help with PL SQL query - Analytical Function or group by
Goto Forum:
  


Current Time: Mon Dec 11 17:12:27 CST 2017

Total time taken to generate the page: 0.02738 seconds