I don't think you can't do this in 2000 in one statement unless you have a column in the table that can serve as a candidate key, in which case you wouldn't have duplicate rows :-). You will need to select a distinct set from the existing table to a temp table delete the existing data and reload it from the temp table. You may need to drop constraints from the table before starting, otherwise the delete will fail, and add them back afterwards.
And don't forget to be a be a good DBA and do a backup of the data first.
You could do it with one delete statement:
but only if you create this trigger first:
this does assume that there are no constraints that would be violated by deleting every row.
answered Oct 29 '09 at 11:43 AM