Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting non distinct rows

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?

like image 696
Luke Wilkinson Avatar asked Aug 02 '11 17:08

Luke Wilkinson


People also ask

How do you delete non distinct records in SQL?

To delete the duplicate rows from the table in SQL Server, you follow these steps: Find duplicate rows using GROUP BY clause or ROW_NUMBER() function. Use DELETE statement to remove the duplicate rows.

How can I delete duplicate rows in a table?

If a table has duplicate rows, we can delete it by using the DELETE statement. In the case, we have a column, which is not the part of group used to evaluate the duplicate records in the table.


1 Answers

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!

like image 121
marc_s Avatar answered Nov 05 '22 04:11

marc_s