Problem solve Get help with specific problems with your technologies, process and projects.

Removing repeated values in a table

What is the most efficient way of removing repeated values in a table? Currently the script I am using only removes duplicates, so it must be run multiple times if the records are triplicated, etc.

Let's say for example that table T has a column C that repeats several times. You want to remove the repeated values.

1) Create a temporary view.

    create view V
        as select count(*) as CNT , C
           from T
           group by C
           having count(*) > 1;
2) Create a temporary table.
    create TABLE T2 as SELECT * from T
3) Try this PL/SQL block.
    declare cursor c_v is select C from V;

     FOR cv in c_v LOOP
          Insert into T2 SELECT * FROM T where T.C = cv.C and ROWNUM = 1;
     END LOOP;
4) DELETE ALL repeated.
     DELETE FROM T  where C in (select C from V)
5) Add only one repeated row.
    insert into T select * from T2;
6) Drop TABLE T2;

7) Drop VIEW V;

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.