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
