From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | "Booth, Robert" <Robert_Booth(at)intuit(dot)com> |
Cc: | "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: NOT IN query issues |
Date: | 2002-05-08 14:37:34 |
Message-ID: | 3CD9382E.2BBB6953@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Robert,
Try:
delete from sharinfo where not exists (
select file__no from allforms where sharinfo.file__no =
allforms.file__no);
Make sure allforms is indexed on file__no.
"Booth, Robert" wrote:
>
> I'm trying to run a DELETE query using NOT IN and it's causing me some
> problems.
>
> Here is the query that I want to run:
>
> delete from sharinfo where file__no not in (select file__no from
> allforms);
>
> When I explain this I get:
>
> Seq Scan on sharinfo (cost=0.00..2225204.88 rows=5239 width=6)
> SubPlan
> -> Seq Scan on allforms (cost=0.00..424.69 rows=8669 width=12)
>
> Which while a very large number does seem to work. But when I run the query
> nothing appears to happen, I've left this query running for hours with no
> response back. Is there a better way to run a query like this (deleting all
> rows that don't appear in a second table)?
>
> Thanks,
> Rob
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-05-08 14:48:16 | Re: Can this be done in one query? |
Previous Message | Darko Prenosil | 2002-05-08 14:11:28 | C trigger |