CODEWITHSUNDEEP
sql-serverduplicatescommon-table-expression
Randy Eckhardt
Randy Eckhardt

Reputation: 21

Trying to find duplicates using CTE in SQL Server 2017

I'm trying to modify the code I found below to allow me to find duplicates that I can delete for rows with RowOrder > 1. I'm getting an error stating Incorrect syntax near ')' on the last line. I'm not sure what I'm doing wrong, please help. Thanks in advance!

original source

WITH OrderedRows
AS
(
SELECT  ID, Carrier, ClaimDate, EmpID, ClaimNumber, LossNumber, TaskID, 
TaskType, TaskGroup, LetterDetails, Outlier, BeginTime, 
EndTime,TemplateUsed, Increment, ROW_NUMBER() OVER (PARTITION BY Carrier, 
ClaimDate, EmpID, ClaimNumber,LossNumber, TaskID, TaskType, TaskGroup, 
LetterDetails, Outlier, BeginTime, EndTime,TemplateUsed, Increment ORDER BY 
ID) as RowOrder
FROM    BillingStaging
)
DELETE FROM OrderedRows
WHERE RowOrder > 1

Upvotes: 2

Views: 11816

Answers (2)

hkravitz
hkravitz

Reputation: 1385

You just defined the CTE but you're not using it, you need to add the delete from statement

WITH OrderedRows
AS
(
     SELECT  ID, Carrier, ClaimDate, EmpID, ClaimNumber, LossNumber, TaskID, 
     TaskType, TaskGroup, LetterDetails, Outlier, BeginTime, 
     EndTime,TemplateUsed, Increment, 
     ROW_NUMBER() OVER (PARTITION BY Carrier, ClaimDate, EmpID,ClaimNumber,LossNumber, TaskID, TaskType, TaskGroup,LetterDetails, Outlier, BeginTime, EndTime,TemplateUsed, Increment ORDER BY ID) as RowOrder
 FROM    BillingStaging
)
DELETE FROM OrderedRows
WHERE RowOrder > 1

Upvotes: 5

Randy Eckhardt
Randy Eckhardt

Reputation: 21

Ugh. Figured it out. Original answer was correct. I was trying to get the syntax correct before using the DELETE statement, but by putting SELECT * FROM OrderedRows, it worked. Thanks for the help. It's been a while since I've done this.

This worked

WITH OrderedRows 
AS
(
SELECT
    ID, ClaimDate, EmpID, ClaimNumber, TaskType, BeginTime, EndTime, Increment,
    ROW_NUMBER() OVER (PARTITION BY ID, ClaimDate, EmpID, ClaimNumber, TaskType, BeginTime,
        EndTime, Increment ORDER BY ID) as RowOrder
FROM BillingStaging
)
SELECT * FROM OrderedRows

Upvotes: 0

Related Questions