Deleting duplicates from a table

• espanolanthony

  SSCarpal Tunnel

  Points: 4204

  More actions

  September 8, 2011 at 2:28 pm

  #244440

  I have a table where there are around 18000 duplicate rows. These duplicates are exactly duplicates of each other including the timestamps. So I cannot delete using the delete statements.

  I have identified the duplicates by this query: and I found 18000 rows.

  SELECT appt_id, event_id, count(*)

  FROM appointments

  GROUP BY appt_id, event_id

  HAVING count(*) > 1

  Now how do i delete all these duplicate rows in a table? Any help is really appreciated

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  September 8, 2011 at 5:25 pm

  #1378963

  You could use something like this CTE

  ;with numbered as(SELECT rowno=row_number() over

  (partition by appt_id, event_id order by appt_id),appt_id, event_id from

  appointments)

  select * from numbered

  Once your sure it is correct, then the SELECT * statement could be changed to DELETE FROM numbered where Rowno > 1

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Ivan Mohapatra

  SSCarpal Tunnel

  Points: 4452

  More actions

  September 8, 2011 at 11:20 pm

  #1379000

  just use this query in low traffic time.

  SELECT distinct * INTO NEWTable FROM OldTable

  delete the old table or drop the old table. if no index is used.

  OR

  truncate the old table after checking the new table.

  and

  insert into oldtable select * from new table

  and drop the old table.