🚀 go-pugleaf

There are lots of unique ways to do this, but the easiest with SQL Server is
to create a clustered index that ignores dup rows...  For example:

  create clustered nonunique index X on myTable(col1) with ignore_dup_row

This will delete ALL duplicate rows leaving one unique row in the
database.  Note that it deletes duplicate rows, not duplicate values
based on the column being indexed.  You will get a message stating 

  Warning:  deleted duplicate row.  Primary key is 'value'

Later,
David Pledger


Les Gainous (lesgainous@earthlink.net) wrote:
: I'm using MS-SQL Server 6.5 (but any generic SQL syntax would be
: appreciated):

: How can I delete multiple occurrences of a row within a table. I know
: how to display the rows which are duplicated:

: 	select		col1,
: 			count(*)
: 	from		myTable
: 	group by	col1
: 	having 		count(*) > 1

: But now that I have that listing, how can I delete the second (or third,
: or...) occurrence of the row--preserving the first occurrence.

: Thanks in advance!
: Les

--
+==============================+=============================================+
| David W. Pledger             | S T R A T E G I C   D A T A   S Y S T E M S |
| davidp@lexis-nexis.com       |      PO Box 498, Springboro, OH  45066      |
| Custom Database Applications |        Phone: (800)253-5624 ext 2940        |
+==============================+=============================================+