SQL Syntax for deleting duplicate records in a table

Showing Answers 1 - 28 of 28 Answers

neelmahato

  • Feb 2nd, 2006
 

Basically There Are Two Methods One Can Delete The Duplicate Records.

Method 1

create table new_table_name as select distinct from old_table_name;

drop old_table_name;

rename new_table_name to old_table_name;

Method 2

delete from table_name1 t1 where exists ( select * from table_name1 t2

where t2.keyvalue1=t1.keyvalue1 and t2.rowid=t1.rowid);

jame

  • Feb 10th, 2006
 

  

to delete duplicate records from a table using create table syntax is not working. can u tell me write query.

  Was this answer useful?  Yes

Kamya

  • Feb 24th, 2006
 

Option 2 given is to be changed to the following:

delete from test t1  where exists ( select * from test t2 where t2.<col1> =t1.<col1> and t2.rowid <>  t1.rowid);

If we just equate the rowid in both the tables(i.e t2.rowid=t1.rowid), then all the records will be deleted instead of just the duplicates.

  Was this answer useful?  Yes

Bindhu

  • Mar 10th, 2006
 

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); Example. Empno Ename 101 Scott 102 Jiyo 103 Millor 104 Jiyo 105 Smith delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename); The output like, Empno Ename 101 Scott 102 Millor 103 Jiyo 104 Smith

  Was this answer useful?  Yes

santhosh

  • Mar 31st, 2006
 

delete * from numbers a where rowid in (select min(rowid) from numbers b group by qty);

  Was this answer useful?  Yes

tarun1282

  • Apr 13th, 2006
 

delete from employees where rowid not in(select max(rowid) from employees group by employee_id,last_name,first_name,salary);here group by clause will contain all the columns which exists in the employees table

  Was this answer useful?  Yes

ram

  • Jul 19th, 2006
 

first create a new_table_name

syntax:

create table new_table_name as select distinct * from old_table_name;

delete old_table_name

syntax:

delete table old_table_name;

renaming new_table_name

syntax:

rename new_table_name to old_table_name;

  Was this answer useful?  Yes

rajakumar_na

  • Nov 4th, 2007
 

Another Example

Try this query

Assume that the table name is emp,

and the table is like this.

id     name

100  raja

100  raja


100  raja

100  raja


Now our query is that to delete duplicate record from emptable

DELETE FROM emp 
WHERE  rowid NOT IN
                              (SELECT MAX(rowid) FROM emp 
                                WHERE id=100)
AND id=100;

  Was this answer useful?  Yes

Another Example

Try this query

Assume that the table name is emp,

and the table is like this.

id name

100 raja
101 kumar
100 raja

100 raja
102 xxx
100 raja


Now our query is that to delete duplicate record from emptable

DELETE FROM emp
WHERE rowid NOT IN (SELECT MAX(rowid) FROM emp WHERE id=100)
AND id=100;

  Was this answer useful?  Yes

zdmytriv

  • Jan 26th, 2008
 

DELETE FROM MYTABLE t1 WHERE t1.id = (SELECT t2.id FROM MYTABLE t2 WHERE (1 < (SELECT COUNT(t3.id) FROM MYTABLE WHERE (t3.data == t2.data))))

  Was this answer useful?  Yes

kunal066

  • Aug 27th, 2008
 

delete from table_name1 t1 where exists
   ( select * from table_name1 t2

          where t2.keyvalue1=t1.keyvalue1

                  and   t2.rowid=t1.rowid);

  Was this answer useful?  Yes

dj_dj_dj

  • Mar 18th, 2010
 

Basically deleting duplicate records while creating table sounds inefficiency of the SQL DBA.

To avoid duplication one can provide the column datatype as Unique or Primary key.........Which is the best method upto my knowledge to prevent duplication entry of data.


Regards
Dharmendra Jaiswal.

  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