問題描述
我正在嘗試使用以下查詢從兩個表中刪除幾行
I am trying to delete a few rows from two tables using the following query
Delete top(3) ss
from stage.SubmitItemData ss
INNER JOIN stage.SubmitItems s (NOLOCK) on ss.SubmitItemId = s.SubmitItemId
where s.AgencyCode = 'NC0860000' and s.StatusId = 8
如果我刪除參數 s.AgencyCode 和 s.StatusId ，我感到困惑的地方是查詢執行沒有問題.但是，如果我添加這些參數，我會影響 (0) 行.
Where I am stumped is if I remove the parameters s.AgencyCode and s.StatusId the query executes with no issue. However if I add these parameters I get the (0) rows affected.
我要做的就是控制在任何給定時間刪除的記錄數.top(n) 不是最好的方法，因為它看起來好像需要訂購才能工作?為這種類型的刪除創建一個循環會更好嗎?
All I am trying to do is to control the number of records deleted at any given time. Is top(n) not the best approach as it looks as if it requires ordering to work? Would it be better to create a loop for this type of delete?
感謝您的任何建議.
推薦答案
DELETE TOP (3)
FROM stage.SubmitItemData
WHERE
EXISTS (SELECT 1
FROM stage.SubmitItems
WHERE SubmitItemId = SubmitItemData.SubmitItemId
AND AgencyCode = 'NC0860000'
AND StatusId = 8)
或者你可以這樣做......
Or you could do something like this......
DELETE TOP(3) FROM ss
FROM stage.SubmitItemData ss
INNER JOIN stage.SubmitItems s WITH (NOLOCK)
ON ss.SubmitItemId = s.SubmitItemId
where s.AgencyCode = 'NC0860000' and s.StatusId = 8
這篇關于如何使用內部聯接刪除前(N)行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！