Re: DELETE FROM t WHERE EXISTS

delete from clp where commit_date < (select commit_date from clp order
by commit_date offset 99 limit 1);

Robert Treat

On Fri, 2003-02-28 at 12:16, 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.
> 
> 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?
> -- 
> Dan Langille : http://www.langille.org/
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html