Is there a way I can turn the below
SELECT statement into a
I would like to delete the corresponding returned records from the
Since I used derived tables I can't reference the
To summarize, the 2 physical tables used in the below query have identical structures. Only difference is
DUPS_Claims is a subset of
DUPS_Claims contains duplicate records found in
Stage_Claims. If a record exists 3 times in
Stage_Claims, we will have that record 3 times in
DUPS_Claims as well.
Stage_Claims contains all records including the duplicate records in
I would like to remove the duplicate records from
Stage_Claims leaving only 1 unique record for every duplicated record.
Stage_Claims has just short of 1 million rows so I do not want to use Row_Number / Partition on the entire table as it takes over 2 minutes to run.
The below query I have runs in about 15 seconds and successfully identifies only the duplicate records (not including the original unique record we want to keep) but I have not been able to figure out how to delete the records that are returned from SC.
Is it possible or should I just take a different approach?
SELECT * FROM ( SELECT RN = ROW_NUMBER() OVER ( PARTITION BY SC.ID ORDER BY SC.id ) ,SC.* FROM [ETL].[Stage_Claims] SC WHERE ID IN ( SELECT ID FROM ( SELECT RN = ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY id ) ,ID FROM [ETL].[DUPS_Claims] ) AS t1 WHERE RN > 1 ) ) AS t2 WHERE RN > 1