Delete via 'IN' list vs 1 by 1

User, date Message
Written by miked
1 year ago
Category: Feature discussion
16 posts since Mon, 09 Nov 09
working in remote db over slow connection so the way that deletes currently happens jumps out at me.

Right now each record selected for delete is handled in its own query

So if I delete 20 rows there are 20 delete statements run. On a remote system this can take awhile. Each delete takes about a second to complete. Deleting 200 records is a long wait....

delete from <table> where id = 1;
delete from <table> where id = 2;
delete from <table> where id = 3;

Possible to build a list of the primary key values for the rows selected, and then add those to a list of id's passed to a single delete statement?


delete from <table> where id in (1,2,3,4,5);

This style of delete would really speed things up in this non-standard use case I have going on that I find myself in quite a bit lately
Written by ansgar
1 year ago
5010 posts since Fri, 07 Apr 06
I'm afraid that the complex logic to detect the primary key/unique key values for a WHERE clause prevents us from using the IN() syntax for automatic DELETEs.

Please login to leave a reply, or register at first.