Deleting non distinct rows

Question

I have a table that has a unique non-clustered index and 4 of the columns are listed in this index. I want to update a large number of rows in the table. If I do so, they will no longer be distinct, therefore the update fails because of the index.

I am wanting to disable the index and then delete the oldest duplicate rows. Here's my query so far:

SELECT t.itemid, t.fieldid, t.version, updated
FROM dbo.VersionedFields w
inner JOIN
(
    SELECT itemid, fieldid, version, COUNT(*) AS QTY
    FROM dbo.VersionedFields
    GROUP BY itemid, fieldid, version
    HAVING COUNT(*) > 1
) t 
on w.itemid = t.itemid and w.fieldid = t.fieldid and w.version = t.version

The select inside the inner join returns the right number of records that we want to delete, but groups them so there is actually twice the amount.

After the join it shows all the records but all I want to delete is the oldest ones?

How can this be done?

Answer 1

If you say SQL (Structured Query Language), but really mean SQL Server (the Microsoft relatinonal database system) by it, and if you're using SQL Server 2005 or newer, you can use a CTE (Common Table Expression) for this purpose.

With this CTE, you can partition your data by some criteria - i.e. your ItemId (or a combination of columns) - and have SQL Server number all your rows starting at 1 for each of those partitions, ordered by some other criteria - i.e. probably version (or some other column).

So try something like this:

;WITH PartitionedData AS
(
    SELECT 
       itemid, fieldid, version, 
       ROW_NUMBER() OVER(PARTITION BY ItemId ORDER BY version DESC) AS 'RowNum'
    FROM dbo.VersionedFields
)
DELETE FROM PartitionedData
WHERE RowNum > 1

Basically, you're partitioning your data by some criteria and numbering each partition, starting at 1 for each new partition, ordered by some other criteria (e.g. Date or Version).

So for each "partition" of data, the "newest" entry has RowNum = 1, and any others that belongs into the same partition (by means of having the same partitino values) will have sequentially numbered values from 2 up to however many rows there are in that partition.

If you want to keep only the newest entry - delete anything with a RowNum larger than 1 and you're done!

Answer 2

In SQL Server 2005 and above:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY itemid, fieldid, version ORDER BY updated DESC) AS rn
        FROM    versionedFields
        )
DELETE
FROM    q
WHERE   rn > 1

Answer 3

Try something like:

DELETE FROM dbo.VersionedFields w WHERE w.version < (SELECT MAX(version) FROM dbo.VersionedFields)

Ofcourse, you'd want to limit the MAX(version) to only the versions of the field you're wanting to delete.

Answer 4

You probably need to look at this Stack Overflow answer (delete earlier of duplicate rows).

Essentially the technique uses grouping (or optionally, windowing) to find the minimum id value of a group in order to delete it. It may be more accurate to delete rows where the value <> max(row identifier).

So:

1. Drop unique index
2. Load data
3. Delete data using the grouping mechanism (ideally in a transaction, so that you can rollback if there is a mistake), then commit
4. Recreate the index.

Note that recreating an index on a big table can take a long time.