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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Eckermann | 2003-02-28 19:30:56 | Re: DELETE FROM t WHERE EXISTS |
Previous Message | Guy Fraser | 2003-02-28 17:57:05 | Re: WebDB, iAS |