How to delete duplicate records from table

Method-01

DELETE FROM custoomers
      WHERE ROWID IN (
               SELECT “rowid”
                 FROM (SELECT “rowid”, rank_n
                         FROM (SELECT RANK () OVER (PARTITION BY custoomer_id ORDER BY ROWID)
                                                                       rank_n,
                                      ROWID AS “rowid”
                                 FROM customers
                                WHERE custoomer_id IN (SELECT   custoomer_id
                                                           FROM custoomers
                                                       GROUP BY custoomer_id
                                                         HAVING COUNT (*) > 1)))
                WHERE rank_n > 1)
Method-02 
DELETE FROM Customers t
      WHERE t.ROWID NOT IN (
               SELECT MIN (b.ROWID)
                 FROM Customers b
                WHERE b.ser_no = t.ser_no
                  AND b.fst_nm = t.fst_nm
                  AND b.deptid = t.deptid
                  AND b.cmnt = t.cmnt);

Method-03

SELECT book_unique_id, page_seq_nbr, image_key
  FROM books a
 WHERE ROWID >
              (SELECT MIN (ROWID)
                 FROM books b
                WHERE b.key1 = a.key1 AND b.key2 = a.key2 AND b.key3 = a.key3);

Advertisements

About Oracle Technology Blogs
Oracle Application Development

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: