CODEWITHSUNDEEP
sql-servert-sql
newguy
newguy

Reputation: 5976

Rewriting a T-SQL query that delete more than 30k rows

If I want to delete as many as 30k rows in a table in TSQL, I can't use

delete from myTable where Id in (1,2,3,...,30k)

because the number of IDs in the IN clause is too big, according to this article http://support.microsoft.com/kb/288095

But how can I rewrite my query to delete the rows using a temporary table and table joins?

EDIT: The IDs are not in order. They are picked by certain logic so they are just some arbitrary IDs in myTable.

Upvotes: 1

Views: 394

Answers (2)

Robbie Tapping
Robbie Tapping

Reputation: 2556

SELECT DISTINCT ID
INTO #tmpIDs
FROM tbl_IDTable
WHERE ID BETWEEN 1 AND 30000

DELETE MYTABLE 
FROM MYTABLE t
INNER JOIN #tmpIDs d ON d.id = t.id

DROP TABLE #tmpIDs

* IF IT IS A QUERY INSIDE ANOTHER QUERY *****

 DELETE MYTABLE 
    FROM MYTABLE t
    INNER JOIN (SELECT DISTINCT ID
                FROM MYTABLE
                WHERE ID BETWEEN 1 AND 30000) d d.id = t.id

Upvotes: 7

oskarpearson
oskarpearson

Reputation: 284

If you are hitting only that specific limit, you could try something like this:

delete from myTable where ID = 1 or ID = 2 or ID = 3 or ID = ...

You may just hit a different limit then, though.

Best solution, imho is to split things into multiple queries when you generate the sql and run things in separate batches.

Upvotes: 0

Related Questions