Skip site navigation (1) Skip section navigation (2)

Re: DELETE FROM t WHERE EXISTS

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Dan Langille <dan(at)langille(dot)org>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: DELETE FROM t WHERE EXISTS
Date: 2003-02-28 20:34:45
Message-ID: 20030228123036.L2988-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Fri, 28 Feb 2003, Dan Langille 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.

As long as the inner select returns at least 1 result NOT EXISTS is
going to return false (you haven't correlated the two queries at all).

> 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?

Possibly something like:
DELETE FROM clp
 WHERE NOT EXISTS (
  select * from (select * from clp order by commit_date limit 100) tmp
  where tmp.commit_log_id = clp.commit_log_id
 );

But I haven't tried it for stupid errors, and am not sure that it'd end up
being any better than NOT IN anyway.




In response to

Responses

pgsql-sql by date

Next:From: Robert TreatDate: 2003-02-28 20:36:04
Subject: Re: DELETE FROM t WHERE EXISTS
Previous:From: Tom LaneDate: 2003-02-28 20:11:53
Subject: Re: DELETE FROM t WHERE EXISTS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group