X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 952DF476183 for ; Fri, 28 Feb 2003 16:59:23 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 38921-02 for ; Fri, 28 Feb 2003 16:59:12 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 097D747618E for ; Fri, 28 Feb 2003 15:36:32 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h1SL5D6P009726; Fri, 28 Feb 2003 16:05:14 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h1SKa4p24061; Fri, 28 Feb 2003 15:36:08 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id F3HTH4S8; Fri, 28 Feb 2003 15:36:04 -0500 Subject: Re: DELETE FROM t WHERE EXISTS From: Robert Treat To: Dan Langille Cc: pgsql-sql@postgresql.org In-Reply-To: <3E5F530B.1649.424D34C1@localhost> References: <3E5F530B.1649.424D34C1@localhost> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 28 Feb 2003 15:36:04 -0500 Message-Id: <1046464564.2038.28.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/624 X-Sequence-Number: 12022 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