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

Re: DELETE FROM t WHERE EXISTS

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 (view raw or flat)
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/

In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2003-02-28 20:11:53
Subject: Re: DELETE FROM t WHERE EXISTS
Previous:From: Jakub OuhrabkaDate: 2003-02-28 19:25:27
Subject: Re: DELETE FROM t WHERE EXISTS

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