SQL Server 将选择转换为删除

mwngjboj 于 2023-01-16 发布在其他

关注(0)|答案(3)|浏览(157)

我需要使用这个SQL select语句来删除它找到的记录：

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)

任何帮助都将不胜感激。

sql-server

来源：https://stackoverflow.com/questions/42004810/convert-select-to-delete

3条答案

按热度按时间

wecizke31#

在“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)

赞(0）回复(0）举报 2023-01-16

xfyts7mz2#

您可以使用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

这也是轻松检查所选记录的好方法：

;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

赞(0）回复(0）举报 2023-01-16

sycxhyv73#

您只需要删除在dbo.X_ApplyFiles表中找不到ID的记录

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

赞(0）回复(0）举报 2023-01-16

我来回答

SQL Server 将选择转换为删除

3条答案

相关问题

热门标签

最新问答