X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 631D6476120
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 16:54:28 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 35865-07 for <pgsql-sql@postgresql.org>;
	Fri, 28 Feb 2003 16:54:15 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 42741476130
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 15:29:31 -0500 (EST)
Received: from lamer.pl (localhost [127.0.0.1])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 54CA22BDDF; Fri, 28 Feb 2003 21:28:27 +0100 (CET)
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Dan Langille" <dan@langille.org>, pgsql-sql@postgresql.org
Subject: Re: DELETE FROM  t WHERE EXISTS
Date: Fri, 28 Feb 2003 21:28:27 +0800
Message-Id: <20030228212827.M96860@klaster.net>
In-Reply-To: <3E5F530B.1649.424D34C1@localhost>
References: <3E5F530B.1649.424D34C1@localhost>
X-Mailer: Open WebMail 1.62 20020221
X-OriginatingIP: 217.96.97.171 (jasiek)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/623
X-Sequence-Number: 12021

> 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