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 T3) 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.