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.

-- 
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED]
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to