CODEWITHSUNDEEP
sqlsql-serversql-server-2008
PigsIncorporated
PigsIncorporated

Reputation: 153

Convert Select to Delete

I have this SQL select statement that I need to use to delete the records it finds:

SELECT dbo.X_Files.FileID, dbo.X_Files.FileX, dbo.X_ApplyFiles.idPRJ
FROM             dbo.X_Files 
LEFT OUTER JOIN  dbo.X_ApplyFiles ON dbo.X_Files.FileID = dbo.X_ApplyFiles.FileID
WHERE  (dbo.X_ApplyFiles.idPRJ IS NULL) 
AND    (dbo.X_Files.FileX = @FileX)

Any help would be appreciated.

Upvotes: 1

Views: 2235

Answers (3)

Siyual
Siyual

Reputation: 16917

You can easily do this with a CTE:

;With ToDelete As
(
    SELECT dbo.X_Files.*
    FROM             dbo.X_Files 
    LEFT OUTER JOIN  dbo.X_ApplyFiles ON dbo.X_Files.FileID = dbo.X_ApplyFiles.FileID
    WHERE  (dbo.X_ApplyFiles.idPRJ IS NULL) 
    AND    (dbo.X_Files.FileX = @FileX)
)
Delete  ToDelete

It's also a good way to easily check the records you've selected:

;With ToDelete As
(
    SELECT dbo.X_Files.*
    FROM             dbo.X_Files 
    LEFT OUTER JOIN  dbo.X_ApplyFiles ON dbo.X_Files.FileID = dbo.X_ApplyFiles.FileID
    WHERE  (dbo.X_ApplyFiles.idPRJ IS NULL) 
    AND    (dbo.X_Files.FileX = @FileX)
)
Select    *
From      ToDelete

Upvotes: 2

Teddy Sterne
Teddy Sterne

Reputation: 14221

You just need to delete the records with an ID that is not found int the dbo.X_ApplyFiles table

DELETE FROM dbo.X_Files
WHERE dbo.X_Files.FileID NOT IN (SELECT dbo.X_ApplyFiles.FileID FROM dbo.X_ApplyFiles)

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

Specify the table name just after 'delete':

delete dbo.X_Files
FROM             dbo.X_Files
LEFT OUTER JOIN  dbo.X_ApplyFiles ON dbo.X_Files.FileID = dbo.X_ApplyFiles.FileID
WHERE  (dbo.X_ApplyFiles.idPRJ IS NULL) 
AND    (dbo.X_Files.FileX = @FileX)

Upvotes: 2

Related Questions