From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: DELETE with JOIN |
Date: | 2008-08-07 19:00:35 |
Message-ID: | 489B4653.8070805@sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
felix(at)crowfix(dot)com wrote:
> On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote:
>
>> 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])
>
> It may come to something like that, but I figure handing over hubdreds
> of static IDs is probably worse for the planner than an expression,
> and it's ugly as sin :-)
>
> I tried using "%" for a mod function, but that seems to not be a
> universally recognized operator.
Could you not achieve the same result with a LIMIT on subSELECT and
reissue the command until there is nothing to delete?
Is b_id already unique; or should you be using DISTINCT or GROUP BY on
the subSELECT?
DELETE FROM a WHERE a.b_id IN (SELECT DISTINCT id FROM b WHERE second_id
= ? LIMIT 1000)
DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?
GROUP BY id LIMIT 1000)
If you're really desperate; is it possible to alter table 'a' to add
column b_id; populate it; delete your rows without a join; then drop the
column?
From | Date | Subject | |
---|---|---|---|
Next Message | felix | 2008-08-07 19:12:07 | Re: DELETE with JOIN |
Previous Message | felix | 2008-08-07 18:37:57 | Re: DELETE with JOIN |