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 |
|
samiran_cts
Messages: 52 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 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, 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 |
|
quirks
Messages: 82 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 #665143 is a reply to message #665142] |
Wed, 23 August 2017 05:24 |
cookiemonster
Messages: 13943 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 |
cookiemonster
Messages: 13943 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 #665177 is a reply to message #665166] |
Thu, 24 August 2017 07:48 |
joy_division
Messages: 4963 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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 08:07:02 CDT 2024
|