SQL - How will you delete duplicating rows from a base table?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 12 of 171

How will you delete duplicating rows from a base table?

delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or

delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

Total Answers and Comments: 4

Last Update: August 18, 2007

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

March 26, 2005 14:38:38

AjayLebaka

RE: How will you delete duplicating rows from a base table?

DELETE TableName WHERE rowid NOT IN ( SELECT MIN(rowid) FROM TableName GROUP BY ColumnName);

Is this answer useful? Yes | No

December 12, 2006 08:37:20

rampratap409

Member Since: September 2006 Contribution: 111

RE: How will you delete duplicating rows from a base t...

delete <tablename> a

where rowid <> ( select max(rowid) from <sametablename)

where <columnname> a.<columnname);

Is this answer useful? Yes | No

July 17, 2007 10:25:10

RE: How will you delete duplicating rows from a base t...

The above examples can be used only if there is only one column having repeating values. If all the columns have repeating values i.e. there is total duplicacy of rows then they fail. The following code can be used for any tables only the column name needs to be changed

CREATE TABLE EMP_TEST (a NUMBER b NUMBER);

DECLARE

CURSOR c1 IS SELECT * FROM emp_test FOR UPDATE OF a b NOWAIT;

CURSOR c2 IS SELECT * FROM emp_test FOR UPDATE OF a b NOWAIT;

Emp_rec1 c1 ROWTYPE;

Emp_rec2 c2 ROWTYPE;

N NUMBER;

BEGIN

FOR i IN c1 LOOP

N: 0;

FOR j IN c2 LOOPIF

(i.a j.a AND i.b j.b AND N 1) THENDELETE FROM emp_test WHERE CURRENT OF c2;

COMMIT;

END IF;

IF (i.a j.a AND i.b j.b AND N 0) THEN

N: 1;

END IF;END

LOOP;

END LOOP;

END;

Is this answer useful? Yes | No

August 18, 2007 06:24:37

syam sundar

RE: How will you delete duplicating rows from a base t...

Delete table_name t1
where rowid>(select rowid from table_name t2)
and t1.column1 t2.column1

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank