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);
  • Interview Candidate
  • 13
  • 5231

Showing Answers 1 - 13 of 13 Answers

AjayLebaka

  • Mar 26th, 2005
 

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

GT

  • Jul 17th, 2007
 

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;



  Was this answer useful?  Yes

syam sundar

  • Aug 18th, 2007
 

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions