CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Diego
Diego

Reputation: 36146

Batch Delete from tableA based on data on tableB

I'm trying to implement a batch delete. I found this code on the internet: 

DECLARE @rowcount int = 1
WHILE (@rowcount != 0 ) BEGIN
    DELETE T1
    FROM (SELECT TOP (50) * FROM Orders WHERE OrderCity = @city) T1

    SELECT @rowcount = @@ROWCOUNT
END

the idea is to delete all orders from @city

It seems to work fine but on my reality, I need to delete from Orders where OrderCity in (select ID from SomeOtherTable)

If I try to do the same, it works but it takes a lot of time because SomeOtherTable will contain around 1.5 million rows and the data is being deleted from the main table, so it doesnt get any smaller (it does not contains cities, its another thing).

I also cant join both tables because it wont run saying that more than one table will be affected.

So basically my question is: Is there anyway to batch delete from tableA where tableA.ID IN (select ID from tableB)

Upvotes: 1

Views: 432

Answers (3)

bummi
bummi

Reputation: 27377

Yes you can do it without join as:

DELETE tableA
FROM tableB 
WHERE tableA.ID = tableB.ID

Upvotes: 1

Sarin
Sarin

Reputation: 1285

You should be able to delete based on a join. Try

DELETE FROM tableA
FROM tableA A
JOIN tableB B ON A.ID = B.ID

Also, if tableB has ~ a million rows, it would really help if you have an index on the ID column.

Upvotes: 0

Sonu Kukreja
Sonu Kukreja

Reputation: 31

Delete Order
FROM
Order INNER JOIN SomeOtherTable ON Order.OrderCity = SomeOtherTable.ID

This could solve your problem

Upvotes: 0

Related Questions