At 10:05 AM 8/7/2008, [EMAIL PROTECTED] wrote:
Date: Thu, 7 Aug 2008 09:14:49 -0700
From: [EMAIL PROTECTED]
To: pgsql-sql@postgresql.org
Subject: DELETE with JOIN
Message-ID: <[EMAIL PROTECTED]>
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
