How to Delete Top(N) rows with an inner join?

Question

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

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.

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?

Thanks for any suggestions.

Answer 1

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