如何使用内部联接删除前(N)行?

问题描述

我正在尝试使用以下查询从两个表中删除几行

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