X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AAC6D475F25
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 13:27:56 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 87674-08 for <pgsql-sql@postgresql.org>;
	Fri, 28 Feb 2003 13:27:45 -0500 (EST)
Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150])
	by postgresql.org (Postfix) with ESMTP id 5D6E6475FF9
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 12:12:33 -0500 (EST)
Received: from wocker (wocker.unixathome.org [192.168.0.99])
	by bast.unixathome.org (Postfix) with ESMTP id D26053D28
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 12:13:07 -0500 (EST)
From: "Dan Langille" <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Fri, 28 Feb 2003 12:16:11 -0500
MIME-Version: 1.0
Subject: DELETE FROM  t WHERE EXISTS
Message-ID: <3E5F530B.1649.424D34C1@localhost>
X-mailer: Pegasus Mail for Windows (v4.02a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/617
X-Sequence-Number: 12015

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/