• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

delete duplicate rows

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What's the query for identifying and deleting duplicate rows in a table.
For eg if i have 10 similar rows in a table,then to identify them and deleting 9 rows out of 10.
Thanks in advance
Sumeet Mittal
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
try this
delete from testtable a
where exists
(select 1 from testtable b
where b.dup_value = a.dup_value
and b.rowid > a.rowid)
Regards
Beksy
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Detecting duplicates
I'll show you two of the most useful SQL queries you'll ever learn. (Shh! Don't tell everybody!)
First assume a table with the following data. (No key fields.)

To find duplicates, use the following query.

To use this query in the more general case, just modify the list of fields in the GROUP BY clause (giving careful thought to key vs. nonkey fields and how you're defining a "duplicate").
To see the entire row for each of these "duplicates," just nest the above query:

For our present data, this query isn't particularly interesting because our table only has 1 field. It becomes more useful when you're dealing with rows that match on some fields but not others.
Deleting duplicates
Another way of "deleting" duplicates is to insert into a temp table, then rename the table.

Give careful thought to what fields are in the select (distinct) list because it will affect what's considered a duplicate.
 
reply
    Bookmark Topic Watch Topic
  • New Topic