deleting duplicates

I'm having trouble deleting duplicates from enterprise manager. I get "Key
column information is insufficient or incorrect. Too many rows were affected
by update." messages.
I can run delete queries through query analyzer but that will delete the
original reocrd and duplicate. The table in question has 3 columns (all 3
columns show dup data) with no indexes and about 15 original rows duplicated.
Question 1: Why can't I delete from enterprise manager? The help file is
useless.
Question 2: What is the T-SQL for deleting the duplicate record (but leaving
the original)?
On Wed, 29 Mar 2006 20:01:01 -0800, carl wrote:
Hi Carl,
I'll address your questions in reverse order.

>Question 2: What is the T-SQL for deleting the duplicate record (but leaving
>the original)?
There is none (but see below form some kludges).
In the DELETE statement, you use a WHERE clause to tell SQL Server which
row(s) to delete. If two or more rows have the exact same data in ALL
columns, then any WHERE clause that matches one will match the other as
well. That's but one of the reasons why each table should always have at
least one PRIMARY KEY or UNIQUE constraint.

>Question 1: Why can't I delete from enterprise manager? The help file is
>useless.
Since Enterprise Manager is just a fancy front end that translates your
mouse clicks to queries, it has the same limitation as you have when
writing T-SQL statements in Query Analyzer.

>I can run delete queries through query analyzer but that will delete the
>original reocrd and duplicate. The table in question has 3 columns (all 3
>columns show dup data) with no indexes and about 15 original rows duplicated.
To delete just a single duplicated row, you can use this kludge:
SET ROWCOUNT 1
DELETE FROM MyTable
WHERE Column1 = ...
AND Column2 = ...
....
SET ROWCOUNT 0
If you want to get rid of *ALL* duplicates, rename the table, then
recreate it (don't forget to add the constraints this time!!) and move
the data back, using DISTINCT to squish the dups:
sp_rename 'MyTable', 'MyTableTMP', 'OBJECT'
go
CREATE TABLE MyTable
(Column1 int NOT NULL,
...
PRIMARY KEY (Column1, Column2)
)
go
INSERT INTO MyTable (Column1, ...)
SELECT DISTINCT Column1, ...
FROM MyTableTMP
go
DROP TABLE MyTableTMP
go
Hugo Kornelis, SQL Server MVP