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.

Do you get any rows if you change it to a `select`? Do you actually have rows that match? — Blorgbeard, Feb 26 '14 at 20:54
yes there is data within the table returned on a select if I remove the parameters for agency code and status the delete works (with matching data) — rlcrews, Feb 26 '14 at 21:02
Is there data returned on a select if you *don't* remove the parameters? — Blorgbeard, Feb 26 '14 at 21:06
yes data is returned in a select statement with or without the parameters — rlcrews, Feb 26 '14 at 21:11

M.Ali · Accepted Answer · 2014-02-26T21:20:18.403

9

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

edited Feb 26 '14 at 21:20

answered Feb 26 '14 at 20:57

M.Ali

67,945
13
101
127

no neither of these queries worked either. Both returned the same row count in the select (51396) before and after the delete with the delete statement itself returning 0 rows affected – rlcrews Feb 26 '14 at 21:10
@rlcrews The queries are fine 100%, it means there are no rows which satisfy the criteria. Just do a simple select instead of Delete and see if it returns any rows. – M.Ali Feb 26 '14 at 21:13

Aftab Ahmed · Answer 2 · 2014-02-26T21:18:33.003

0

try this one

DELETE FROM stage.SubmitItemData
WHERE SubmitItemId IN
(SELECT TOP 3 SubmitItemId  
from stage.SubmitItemData ss 
INNER JOIN stage.SubmitItems s (NOLOCK) on ss.SubmitItemId = s.SubmitItemId 
and s.AgencyCode = 'NC0860000'
and s.StatusId = 8
order by ASC);

edited Feb 26 '14 at 21:18

answered Feb 26 '14 at 20:55

Aftab Ahmed

1,727
11
15

no parse errors but the same result of no records being deleted – rlcrews Feb 26 '14 at 21:06
first try to select top 3 rows. i think there no rows exist for this condition. what's the type of StatusId column ?? – Aftab Ahmed Feb 26 '14 at 21:07
The select statements work fine data is returned. A delete statement without the TOP(N) works fine however I need to control the number or records that are deleted. I assume a TOP(N) approach is the correct way to do this – rlcrews Feb 26 '14 at 21:13

Shantanu Gupta · Answer 3 · 2014-02-26T21:21:56.587

0

;WITH cte
AS
(
    select *, Row_number() over (order by s.SubmitItemId) As Rno
    from stage.SubmitItemData ss 
    INNER JOIN stage.SubmitItems s (NOLOCK) on ss.SubmitItemId = s.SubmitItemId 
    where s.AgencyCode = 'NC0860000' and s.StatusId = 8
)
DELETE FROM cte where Rno <=3

edited Feb 26 '14 at 21:21

answered Feb 26 '14 at 21:00

Shantanu Gupta

20,688
54
182
286

In the current DB this table can return a row count ranging from a few thousand rows to well over 10 million when running a select count(*) statement using the same criteria – rlcrews Feb 26 '14 at 21:04

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

3 Answers3

Linked