Styjun

I need to keep only one row where a value is duplicated, i.e. delete every row that is not the "first" one with the duplicate value.

I have the following table called ART_NEW:

PHARMACODE | GTIN | Other stuff
111 1234 ...

• PHARMACODE is the primary key (integer)


• GTIN is another integer, suposedly unique, but collisions sometimes happen.

The data source for this table is preparing to transition from PHARMACODE to GTIN as the primary key, but this transition is not yet complete. I am not in control of the source in any way.

To use GTIN as a PK in some application, I need to delete all but one of the rows where GTIN is duplicated (all rows with the same GTIN describe the same product, with slight variations in its description, so it doesn't matter which row is deleted and which one is not, as long as I end up with only one row for a specific GTIN value).

The following query does exactly what I need, but is horrendously slow (> 1 minute execution time on 350'000 records with 120 rows with duplicate GTIN values):

DELETE *
FROM ART_NEW
WHERE ART_NEW.PHARMACODE IN 
 (SELECT PHARMACODE FROM
 (SELECT
 ART_NEW.[PHARMACODE],
 ART_NEW.GTIN,
 (SELECT Count(*)
 FROM ART_NEW As X
 WHERE X.GTIN = ART_NEW.GTIN
 AND X.PHARMACODE <= ART_NEW.PHARMACODE) AS SeqNo
 FROM ART_NEW
 WHERE ART_NEW.[GTIN] In 
 (SELECT [GTIN] FROM [ART_NEW] As Tmp GROUP BY [GTIN] HAVING Count(*)>1)) 
 WHERE SeqNo > 1);

How can I optimise this code? Alternatively, is there a better way to achieve my goal of deleting every row but one with duplicate GTIN values?

looks like PHARMACODE is numeric, that should work with standard SQL:

delete * from ART_NEW 
where ART_NEW.PHARMACODE NOT IN 
(select a3.PHARMACODE from (select min(a2.PHARMACODE) as PHARMACODE, a2.GTIN from ART_NEW a2 group by a2.GTIN) a3)
;

select * from ART_NEW 
where ART_NEW.PHARMACODE IN 
(select a3.PHARMACODE from (select min(a2.PHARMACODE) as PHARMACODE, a2.GTIN from ART_NEW a2 group by a2.GTIN) a3)
;

Also, if you are able to use Partitions, try making Where for row=1 with OVER (PARTITION BY GTIN).

If you don't have a primary key on the table this is an easy way to delete duplicate rows. Set your PartitionBy to be what you would consider would be your uniqueidentifier and your order by to be your sort order making your most top row your valid data and everything that sorts below it be your invalid data to be deleted.

DELETE a
FROM (
 SELECT 
 ROW_NUMBER() OVER (PARTITION BY tableUniqueid ORDER BY dateCreated DESC) AS SEQ,
 *
 FROM myTable
) a
WHERE SEQ > 1