DELETE with SELECT and join in SQL SERVER

Transact-SQL
1

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

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 s.DocumentId =r.DocID
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

2

But what is the primary key of the table?
Why don't you provide DDL to be able to reply?

3

However, trying a solution and assuming that DocumentId is the primary key of the Document table, check if this is a valid solution for you.

;WITH CTE AS
(
	SELECT
		s.DocumentId
		, 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 
		s.DocumentId =r.DocID
	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'
)		
DELETE 
	Document
FROM 
	Document d
INNER JOIN CTE ON
	CTE.DocumentId = d.DocumentId
	AND CTE.DocCount > 1
4

Hi
Thanks. It works.

1 Like
5

Edward64, thank you very much for your feedback.