Anonymous

• Nov 4th, 2006

 

You can avoid duplicate records from table by using disinct keyword.

eg: select distinct uid from table1.

Das

• Nov 6th, 2006

 

delete from table a where a.rowid != (select max(b.rowid) from table b where a.column1=b.column1)

praveen

• Dec 7th, 2006

 

the above given answer is correct but in place of the operator != you use not in opearator, it will deffinetly works.

Guest

• Feb 2nd, 2007

 

create table tab2(a int, b int);delete from tab2 a where (a.rowid) > ( sel min(b.rowid) from tab2 b where a.a=b.a);or delete from tab2 a where (a.rowid) < ( sel max(b.rowid) from tab2 b where a.a=b.a);

Venkat

• Jul 31st, 2007

 

SET ROWCOUNT 1;
               DELETE FROM <TABLENAME>
                           WHERE     <COLOUMN NAME>  IN
                                                      (SELECT     <COLOUMN NAME>
                                                        FROM       <TABLENAME>  
                                                        GROUP BY  <COLOUMN NAME>
                                                        HAVING  count(*) > 1)

Kiran Kumar

• Oct 25th, 2007

 

delete from tablename where rowid not in (select max(rowid) from tablename group by columnname)

sowmya

• Nov 19th, 2007

 

here != works bcoz the inner query returns only a single value. NOT IN is used when multiples values are returned by the inner query

shibraj

 Profile Answers by shibraj 

• Jan 23rd, 2008

 

find the rowid for the duplicate row and delete the max id no keeping the min id. thats it...