328

I have a table with a very large amount of rows. Duplicates are not allowed but due to a problem with how the rows were created I know there are some duplicates in this table. I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have slightly different data but I do not care about that. I still need to keep one of these rows however. SELECT DISTINCT won't work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

Improve this question
0

3 Answers 3

Reset to default
621

You didn't say what version you were using, but in SQL 2005 and above, you can use a common table expression with the OVER Clause. It goes a little something like this:

WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1

Play around with it and see what you get.

(Edit: In an attempt to be helpful, someone edited the ORDER BY clause within the CTE. To be clear, you can order by anything you want here, it needn't be one of the columns returned by the cte. In fact, a common use-case here is that "foo, bar" are the group identifier and "baz" is some sort of time stamp. In order to keep the latest, you'd do ORDER BY baz desc)

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

This will retain the last duplicate row or the first row ?
Just got pulled back to this answer and noticed the question re: which dupe will it retain. As written, it will retain the "first" duplicate row, where "first" means "lowest ordering according to baz". Of course, if ever you're unsure of what will be deleted/retained, turn the delete into a select and make sure. Better safe than sorry.
Don't forget the semi colon before the WITH if its being executed in a batch ie. transaction msdn.microsoft.com/en-us/library/ms175972.aspx
@SumGuy: No; the row number is sufficient. But I like to run this as a select first to inspect what's going to be affected. Also, I just ran a quick test and it appears that SQL Server is smart enough to not carry unneeded columns forward. I determined this by looking at the output column list in an actual execution plan for both the case where I selected everything plus row number and only row number; the two were identical.
With a high row count, the DELETE is probably not recommended (FULL recovery will also cause transaction log to fill). It's probably better to do a SELECT * INTO NewTable FROM cte and then just delete the old table. This will be much faster with very large tables.
|
146

Example query:

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)

Here fields are column on which you want to group the duplicate rows.

Improve this answer

8 Comments

Using this format I got the following error, any ideas? "ERROR 1093 (HY000): You can't specify target table 'Table' for update in FROM clause"
@M1ke MySQL doesn't allow updates to the main table that are referenced from sub queries, but there's a workaround; change 'FROM Table' to 'FROM (SELECT * FROM Table) AS t1' this stores the table in a temporary table so it allows updates to the main table.
Thanks, I actually found that same answer somewhere else but can't remember where - so have a plus 1!
Nice.But what about if we don't have primary key?
@merdan, it works with anything that's sortable. e.g. the following is valid select min(id) from ( select newid() as id union select newid() as id ) as a
|
34

Here's my twist on it, with a runnable example. Note this will only work in the situation where Id is unique, and you have duplicate values in other columns.

DECLARE @SampleData AS TABLE (Id int, Duplicate varchar(20))

INSERT INTO @SampleData
SELECT 1, 'ABC' UNION ALL
SELECT 2, 'ABC' UNION ALL
SELECT 3, 'LMN' UNION ALL
SELECT 4, 'XYZ' UNION ALL
SELECT 5, 'XYZ'

DELETE FROM @SampleData WHERE Id IN (
    SELECT Id FROM (
        SELECT 
            Id
            ,ROW_NUMBER() OVER (PARTITION BY [Duplicate] ORDER BY Id) AS [ItemNumber]
            -- Change the partition columns to include the ones that make the row distinct
        FROM 
            @SampleData
    ) a WHERE ItemNumber > 1 -- Keep only the first unique item
)

SELECT * FROM @SampleData

And the results:

Id          Duplicate
----------- ---------
1           ABC
3           LMN
4           XYZ

Not sure why that's what I thought of first... definitely not the simplest way to go but it works.

Improve this answer

8 Comments

This doesn't preserve one original in duplicates. This deletes the original as well.
Hi @Sandy, have you verified this? I answered four years ago, I can't remember if I tested it on real data or not.
Yes, I checked on real data. This will delete the original, as well.
Can we please delete this post because unless you test and read the comments, i's BLOODY dangerous!
@Fandango68: I believe I've explained the risks in the body of the post. Copy and pasting random Internet code snippets is a dangerous endeavor. You're more than welcome to vote to delete the post to see if the community agrees.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.