DELETE Dublicate Records

1. General Method

  SELECT COUNT(1) FROM (
  SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY
  GROUP BY USERID, aggregatekey HAVING COUNT(1) > 1);
DELETE FROM MYUSAGESUMMARY
WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                          FROM MYUSAGESUMMARY
                          GROUP BY USERID,AGGREGATEKEY);

2. Advanced Customized Method

SELECT COUNT(1) FROM (
SELECT A.USERID,A.aggregatekey,min(B.TOTALOCTETS) MIN_TOTALOCTETS,max(B.TOTALOCTETS) MAX_TOTALOCTETS FROM (
SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY GROUP BY USERID, aggregatekey HAVING COUNT(1) > 1) A, MYUSAGESUMMARY B
WHERE A.USERID = B.USERID
AND A.aggregatekey = B.aggregatekey
GROUP BY A.USERID, A.aggregatekey
having min(B.TOTALOCTETS) <> max(B.TOTALOCTETS));
  ---Max TOTOAL
  SET SERVEROUTPUT ON
  BEGIN
  FOR REC IN (
        SELECT A.USERID,A.aggregatekey,min(B.TOTALOCTETS) MIN_TOTALOCTETS,max(B.TOTALOCTETS) MAX_TOTALOCTETS
        FROM
           (SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY
            GROUP BY USERID, aggregatekey HAVING COUNT(1) >1 1) A,MYUSAGESUMMARY B
            WHERE A.USERID = B.USERID
            AND A.aggregatekey = B.aggregatekey
            GROUP BY A.USERID, A.aggregatekey
            having min(B.TOTALOCTETS) <> max(B.TOTALOCTETS)
            )
LOOP
DBMS_OUTPUT.PUT_LINE('USERID==>'||REC.USERID||' MIN_T ==> '||REC.MIN_TOTALOCTETS||' MAX_T ==>'||REC.MAX_TOTALOCTETS);

DELETE FROM MYUSAGESUMMARY
WHERE USERID = REC.USERID
AND aggregatekey = REC.aggregatekey
AND TOTALOCTETS = REC.MIN_TOTALOCTETS;

END LOOP;

END;
/

Note:-  check the duplicate record in same session and confirm and commit it.
http://www.oratable.com/duplicate-rows-query/
🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.