From: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
To: Dan Langille <dan@langille.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: DELETE FROM t WHERE EXISTS
Date: Fri, 28 Feb 2003 20:25:27 +0100 (CET)
Message-ID: <Pine.LNX.4.44.0302282004020.10206-100000@alibaba> (raw)
In-Reply-To: <3E5F530B.1649.424D34C1@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jouh8664@ss1000.ms.mff.cuni.cz, dan@langille.org
Subject: Re: DELETE FROM t WHERE EXISTS
In-Reply-To: <Pine.LNX.4.44.0302282004020.10206-100000@alibaba>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox