Re: DELETE FROM t WHERE EXISTS

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