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

Re: DELETE FROM t WHERE EXISTS

From: "Tomasz Myrta" <jasiek(at)klaster(dot)net>
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 13:28:27
Message-ID: 20030228212827.M96860@klaster.net (view raw or flat)
Thread:
Lists: pgsql-sql
> 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.
> 
> 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?
delete from clp where commit_date < (select commit_date from clp order by 
commit_date limit 1 offset 100);
Regards,
Tomasz Myrta

In response to

Responses

pgsql-sql by date

Next:From: Montrone, Marc -- 7183Date: 2003-02-28 15:26:08
Subject: Optimizing
Previous:From: valDate: 2003-02-28 13:24:00
Subject: Re: Copy from a SELECT

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