Delete statement with select and Join

Question

I have a select statement below which gives the expected results. I want to convert this statement to a DELETE Statement but getting error. Below is my query. Any help will be appreciated

SELECT t.*
FROM (
SELECT s.* , COUNT(*) OVER (PARTITION BY s.DocumentName, s.SubmitDateTime, s.DocumentType) AS DocCount
FROM Document s
LEFT OUTER JOIN
Staging b ON s.DocumentId = b.DocumentId
LEFT JOIN Rejections r ON r.DocID = b.DocumentId
WHERE b.DocumentId IS NULL
AND r.DocID IS NULL
AND s.SubmitDateTime IS NOT NULL
AND s.InsertDateTime IS NOT NULL
AND s.DocumentName IS NOT NULL
AND s.Description ='Employee Document'
) t
WHERE t.DocCount > 1
ORDER BY t.DocumentName, t.SubmitDateTime

Answer 1

Hi @Benjoe

The DELETE works but I was hoping to and leave one item. The select statement show duplicates. I want to delete and leave one item.

If I understand correctly, it is not appropriate to use COUNT in this query, and the best solution is to use the Row_Number() function.

Try this:

Delete from Document WHERE DocumentId in 
(
SELECT t.DocumentId
FROM (
SELECT s.* , ROW_NUMBER() OVER (PARTITION BY s.DocumentName, s.SubmitDateTime, s.DocumentType) AS [RowCount]
FROM Document s
LEFT OUTER JOIN
Staging b ON s.DocumentId = b.DocumentId
LEFT JOIN Rejections r ON r.DocID = b.DocumentId
WHERE b.DocumentId IS NULL
AND r.DocID IS NULL
AND s.SubmitDateTime IS NOT NULL
AND s.InsertDateTime IS NOT NULL
AND s.DocumentName IS NOT NULL
AND s.Description ='Employee Document'
) t
WHERE t.[RowCount] > 1)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Delete from Document WHERE DocumentId in (

SELECT t.DocumentId
FROM (
SELECT s.* , COUNT(*) OVER (PARTITION BY s.DocumentName, s.SubmitDateTime, s.DocumentType) AS DocCount
FROM Document s
LEFT OUTER JOIN
Staging b ON s.DocumentId = b.DocumentId
LEFT JOIN Rejections r ON r.DocID = b.DocumentId
WHERE b.DocumentId IS NULL
AND r.DocID IS NULL
AND s.SubmitDateTime IS NOT NULL
AND s.InsertDateTime IS NOT NULL
AND s.DocumentName IS NOT NULL
AND s.Description ='Employee Document'
) t
WHERE t.DocCount > 1)