How to delete duplicate records from a table leaving on set of records

Questions by meena.krishnan

Showing Answers 1 - 11 of 11 Answers

suman

  • Jun 13th, 2006
 

you can delete the duplicate rows in a table by writing the query

delete eno from emp where count(*)>1.similar to this.i.e delete the rows who are having the count more than 1.

  Was this answer useful?  Yes

suresh

  • Jun 14th, 2006
 

this query is not working in sqlserver.the error sayscannot use where clause before the delete statement

  Was this answer useful?  Yes

Raghu

  • Jun 29th, 2006
 

Take <Table_Name>='emp'

Delete from emp where rowid not in

(select min(rowid) from emp group by empno);

This will work dude!!!!!

  Was this answer useful?  Yes

Bapi

  • Jun 29th, 2006
 

Delete * from tab_name where rowid in select min(rowid) from tab_name group by (all the columns of table);

  Was this answer useful?  Yes

Nagraj

  • Jun 30th, 2006
 

This can be done using DISTINCT keyword, consider DEPT_NO column in the STUDENTS table..

SELECT DISTINCT DEPT_NO FROM STUDENTS;

~Nags

  Was this answer useful?  Yes

sumit4u

  • Jul 5th, 2006
 

delete from <Table Name>where(select distinct <FieldName> from <TableName>where name ="name of that duplicate field")

not sure

  Was this answer useful?  Yes

Manohar Reddy

  • Aug 7th, 2006
 

    for example take emp table:

    delete from emp where rowid(select min(rowid) from emp group by ename);

  Was this answer useful?  Yes

vidya

  • Oct 30th, 2007
 

Where clause doesnt allow group functions.You need to use Having Clause.

i.e Having count(*)>1

  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