Re: DELETE with JOIN

At 10:05 AM 8/7/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Thu, 7 Aug 2008 09:14:49 -0700
>From: felix(at)crowfix(dot)com
>To: pgsql-sql(at)postgresql(dot)org
>Subject: DELETE with JOIN
>Message-ID: <20080807161449(dot)GA19337(at)crowfix(dot)com>
>
>I want to delete with a join condition. Google shows this is a common
>problem, but the only solutions are either for MySQL or they don't
>work in my situation because there are too many rows selected. I also
>have to make this work on several databases, includeing, grrr, Oracle,
>so non-standard MySQL "solutions" are doubly aggravating.
>
> DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ?
>
>I have tried to do this before and always found a way, usually
>
> DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id =
> ?)
>
>but I have too many rows, millions, in the IN crowd, ha ha, and it
>barfs. EXISTS is no better. At least Oracle barfs, and I haven't got
>to the others yet. I figured I would go with the worst offender
>first, and let me tell you, it is offensive. Dang I wish it were
>postgresql only!
>
>I could write a Dumb Little Test Program (tm) to read in all those IN
>ids and execute a zillion individual DELETE statements, but it would
>be slow as puke and this little delete is going to come up quite often
>now that I have a test program which needs to generate the junky data
>and play with it for several days before deleting it and starting over
>again.

Hi,

Have you tried something where you read in all those "IN id's" and then
group them into blocks (of say 1,000 or 10,000 or whatever number works
best)? Then execute:

DELETE FROM a WHERE a.b_id in ([static_list_of_ids])

Replacing in a loop "[static_list_of_ids]" with each block of 1000 id's
in a comma delimited string? I use this technique sometimes in
middleware and it works pretty well. There's probably a pure-sql
solution in Pg as well but this method should work across any SQL
platform, which seems like one of your requirements.

Steve