From: | Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> |
---|---|
To: | Dan Langille <dan(at)langille(dot)org>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: DELETE FROM t WHERE EXISTS |
Date: | 2003-02-28 19:30:56 |
Message-ID: | 20030228193056.21775.qmail@web20805.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
--- Dan Langille <dan(at)langille(dot)org> wrote:
> Hi folks,
>
> I wanted to delete "old" rows from a table. These
> are the rows I
> want to keep:
>
> SELECT *
> FROM clp
> ORDER BY commit_date
> LIMIT 100
>
> So I tried this:
>
> DELETE FROM clp
> WHERE NOT EXISTS (
> SELECT *
> FROM clp
> ORDER BY commit_date
> LIMIT 100);
>
> Uhh uhh, nothing deleted. I don't understand why.
>
Your WHERE clause will never evaluate to true in this
case, because something will always be returned by the
subselect.
> OK, I can do this instead:
>
> DELETE from clp
> where commit_log_id NOT in (
> SELECT commit_log_id
> FROM clp
> ORDER BY commit_date
> LIMIT 100);
>
> Can you think of a better way?
> --
AFAIK joins cannot be used with DELETEs (but see
below), so you are stuck with a subselect. If you
don't like the performance of the IN, you could do an
EXISTS using a correlated subselect from a subselect,
but that is ugly. Or you could do it in two steps:
SELECT INTO TEMP sometable *
FROM clp
ORDER BY commit_date
LIMIT 100;
DELETE FROM clp
WHERE commit_date = sometable.commit_date;
That last must be converted into a join clause
somehow, but right now I am too lazy to turn on
logging to find out what :-)
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-02-28 20:11:53 | Re: DELETE FROM t WHERE EXISTS |
Previous Message | Jakub Ouhrabka | 2003-02-28 19:25:27 | Re: DELETE FROM t WHERE EXISTS |