X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C8442476121 for ; Fri, 28 Feb 2003 15:27:27 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 19996-06 for ; Fri, 28 Feb 2003 15:27:15 -0500 (EST) Received: from mx.comgate.cz (unknown [193.165.185.50]) by postgresql.org (Postfix) with ESMTP id 14C5E476237 for ; Fri, 28 Feb 2003 14:25:45 -0500 (EST) Received: from kuba (helo=localhost) by mx.comgate.cz with local-esmtp (Exim 3.35 #1 (Debian)) id 18oq8N-0003pb-00; Fri, 28 Feb 2003 20:25:27 +0100 Date: Fri, 28 Feb 2003 20:25:27 +0100 (CET) From: Jakub Ouhrabka X-X-Sender: kuba@alibaba To: Dan Langille Cc: pgsql-sql@postgresql.org Subject: Re: DELETE FROM t WHERE EXISTS In-Reply-To: <3E5F530B.1649.424D34C1@localhost> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/619 X-Sequence-Number: 12017 Hi, > 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. Because for each row in clp is true that the subselect is returning some rows... The subselect is independant on the outer select as you wrote it... > Can you think of a better way? Mark the rows you want to delete first (add a column or use a temp table) and then delete the marked rows, e.g.: create temp table tmp (commit_log_id int, del bool); insert into tmp (commit_log_id, del) select commit_log_id, true from clp; update tmp set del = false from (select commit_log_id from clp order by commit_date limit 100) as del where del.commit_log_id = tmp.commit_log_id; delete from clp where clp.commit_log_id = tmp.commit_log_id and tmp.del = true; or with the extra column: update clp set del = true; update clp set del = false from (select commit_log_id from clp order by commit_date limit 100) as del where del.commit_log_id = clp.commit_log_id; delete from clp where del = true; hth, kuba