Re: deleting duplicates

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
.

Relevant Pages

  • Re: Copy Row Of Data From Table to Table In Same DB
    ... The syntax error is because of the commas in the WHERE clause. ... conditions in the WHERE clause are logical expressions and you have to use ... SELECT column1, ... procedure and the purpose of passing those column parameters. ...
    (comp.databases.ms-sqlserver)
  • Re: Where clause in DataTable.Select method
    ... What does 'From Column1' mean? ... And the table is always going to be the DataTable you are calling ... So the WHERE clause you have in the statement is already what you need. ... > Just curious how I would include a WHERE clause in the filter string of ...
    (microsoft.public.dotnet.framework.adonet)