Skip site navigation (1) Skip section navigation (2)

Re: DELETE FROM t WHERE EXISTS

From: Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz>
To: Dan Langille <dan(at)langille(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: DELETE FROM t WHERE EXISTS
Date: 2003-02-28 19:25:27
Message-ID: Pine.LNX.4.44.0302282004020.10206-100000@alibaba (view raw or flat)
Thread:
Lists: pgsql-sql
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




In response to

pgsql-sql by date

Next:From: Jeff EckermannDate: 2003-02-28 19:30:56
Subject: Re: DELETE FROM t WHERE EXISTS
Previous:From: Guy FraserDate: 2003-02-28 17:57:05
Subject: Re: WebDB, iAS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group