sql for deleting duplicate rows

Comments

• gigsvoo

  February 2002

  DELETE Table1
  WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)
  
  Neo Gigs
  http://communities.msn.com.my/VisualBasicwithNeo
  
  
• MrEd

  February 2002

  : DELETE Table1
  : WHERE (SELECT Count(*) From Table1 WHERE Count(*) > 1)
  :
  : Neo Gigs
  : http://communities.msn.com.my/VisualBasicwithNeo
  :
  :
  
  umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:
  
  delete from table1 a
  where rowid > (
  select min(rowid)
  from table1 b
  where a.keys = b.keys
  );
  
  I think this is oracle specific though.
  
• ryan_j_smith

  March 2002

  This isn't going to accomplish what you want and it is Oracle specific.
  You have to first decide what columns in the table make a duplicate row and then join on ALL of those columns ( in the example col1 and col2 ). This solution is Oracle specific. If you need it for another database you will need to place a primary key on the table ( which makes the query easier ) and then only delete those rows where one primary key is greater or less than another.
  
  delete from MyTable o
  where
  exists (
  select * from MyTable i
  where
  o.col1 = i.col1 and
  o.col2 = i.col2 and
  ... and
  o.rowid < i.rowid )
  );
  
  : umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:
  :
  : delete from table1 a
  : where rowid > (
  : select min(rowid)
  : from table1 b
  : where a.keys = b.keys
  : );
  :
  : I think this is oracle specific though.
  :
  
  
• sumedh

  March 2002

  : This isn't going to accomplish what you want and it is Oracle specific.
  : You have to first decide what columns in the table make a duplicate row and then join on ALL of those columns ( in the example col1 and col2 ). This solution is Oracle specific. If you need it for another database you will need to place a primary key on the table ( which makes the query easier ) and then only delete those rows where one primary key is greater or less than another.
  :
  : delete from MyTable o
  : where
  : exists (
  : select * from MyTable i
  : where
  : o.col1 = i.col1 and
  : o.col2 = i.col2 and
  : ... and
  : o.rowid < i.rowid )
  : );
  :
  : : umm, thats not going to work. It will delete both duplicate rows, where im assuming he wants to leave one of them. Try something like:
  : :
  : : delete from table1 a
  : : where rowid > (
  : : select min(rowid)
  : : from table1 b
  : : where a.keys = b.keys
  : : );
  : :
  : : I think this is oracle specific though.
  : :
  
  just get the 'rowid' of the duplicate rows u want to delete:
  
  [code]
  select rowid from ;
  [/code]
  
  and then use the DELETE command to remove a row with that rowid.....
  stupid solution.....but it works......
  dont do this if u have a lot of records...
  
  -sumedh