Thread: DELETE FROM t WHERE EXISTS
Hi folks, I wanted to delete "old" rows from a table. These are the rows I want to keep: SELECT * FROM clp ORDER BY commit_date LIMIT 100 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. OK, I can do this instead: DELETE from clp where commit_log_id NOT in ( SELECT commit_log_id FROM clp ORDER BY commit_date LIMIT 100); Can you think of a better way? -- Dan Langille : http://www.langille.org/
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
--- Dan Langille <dan@langille.org> wrote: > Hi folks, > > I wanted to delete "old" rows from a table. These > are the rows I > want to keep: > > SELECT * > FROM clp > ORDER BY commit_date > LIMIT 100 > > 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. > Your WHERE clause will never evaluate to true in this case, because something will always be returned by the subselect. > OK, I can do this instead: > > DELETE from clp > where commit_log_id NOT in ( > SELECT commit_log_id > FROM clp > ORDER BY commit_date > LIMIT 100); > > Can you think of a better way? > -- AFAIK joins cannot be used with DELETEs (but see below), so you are stuck with a subselect. If you don't like the performance of the IN, you could do an EXISTS using a correlated subselect from a subselect, but that is ugly. Or you could do it in two steps: SELECT INTO TEMP sometable * FROM clp ORDER BY commit_date LIMIT 100; DELETE FROM clp WHERE commit_date = sometable.commit_date; That last must be converted into a join clause somehow, but right now I am too lazy to turn on logging to find out what :-) __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
"Dan Langille" <dan@langille.org> writes:
> 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 the inner SELECT is a constant: you forgot to make it depend on
the current outer row. So EXISTS succeeds at every row, unless clp is
already empty.
regards, tom lane
On Fri, 28 Feb 2003, Dan Langille wrote: > Hi folks, > > I wanted to delete "old" rows from a table. These are the rows I > want to keep: > > SELECT * > FROM clp > ORDER BY commit_date > LIMIT 100 > > 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. As long as the inner select returns at least 1 result NOT EXISTS is going to return false (you haven't correlated the two queries at all). > OK, I can do this instead: > > DELETE from clp > where commit_log_id NOT in ( > SELECT commit_log_id > FROM clp > ORDER BY commit_date > LIMIT 100); > > Can you think of a better way? Possibly something like: DELETE FROM clpWHERE NOT EXISTS ( select * from (select * from clp order by commit_date limit 100) tmp where tmp.commit_log_id= clp.commit_log_id); But I haven't tried it for stupid errors, and am not sure that it'd end up being any better than NOT IN anyway.
> Hi folks, > > I wanted to delete "old" rows from a table. These are the rows I > want to keep: > > SELECT * > FROM clp > ORDER BY commit_date > LIMIT 100 > > 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. > > OK, I can do this instead: > > DELETE from clp > where commit_log_id NOT in ( > SELECT commit_log_id > FROM clp > ORDER BY commit_date > LIMIT 100); > > Can you think of a better way? delete from clp where commit_date < (select commit_date from clp order by commit_date limit 1 offset 100); Regards, Tomasz Myrta
delete from clp where commit_date < (select commit_date from clp order by commit_date offset 99 limit 1); Robert Treat On Fri, 2003-02-28 at 12:16, Dan Langille wrote: > Hi folks, > > I wanted to delete "old" rows from a table. These are the rows I > want to keep: > > SELECT * > FROM clp > ORDER BY commit_date > LIMIT 100 > > 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. > > OK, I can do this instead: > > DELETE from clp > where commit_log_id NOT in ( > SELECT commit_log_id > FROM clp > ORDER BY commit_date > LIMIT 100); > > Can you think of a better way? > -- > Dan Langille : http://www.langille.org/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
What about select * into temp rows_to_keep from clp order by commit_date limit 100; truncate clp; insert into clp select * from rows_to_keep; Dima. Dan Langille wrote: > Hi folks, > > I wanted to delete "old" rows from a table. These are the rows I > want to keep: > > SELECT * > FROM clp > ORDER BY commit_date > LIMIT 100 > > 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. > > OK, I can do this instead: > > DELETE from clp > where commit_log_id NOT in ( > SELECT commit_log_id > FROM clp > ORDER BY commit_date > LIMIT 100); > > Can you think of a better way?
Ahh yes, thanks for finding that off-by-one situation. Your query is nearly identical to one from Tomasz Myrta. Seq Scan on clp (cost=0.00..2.26 rows=34 width=6) (actual time=7.98..11.42 rows=400 loops=1) Filter: (commit_date < $0) InitPlan -> Limit (cost=5.62..5.62 rows=1 width=8) (actualtime=7.29..7.31 rows=1 loops=1) -> Sort (cost=5.37..5.62 rows=101 width=8) (actual time=6.49..6.90 rows=101 loops=1) Sort Key: commit_date -> Seq Scan on clp (cost=0.00..2.01rows=101 width=8) (actual time=0.21..3.73 rows=500 loops=1)Total runtime: 15.36 msec (8 rows) On Fri, 28 Feb 2003, Robert Treat wrote: > delete from clp where commit_date < (select commit_date from clp order > by commit_date offset 99 limit 1); > > Robert Treat > > On Fri, 2003-02-28 at 12:16, Dan Langille wrote: > > Hi folks, > > > > I wanted to delete "old" rows from a table. These are the rows I > > want to keep: > > > > SELECT * > > FROM clp > > ORDER BY commit_date > > LIMIT 100 > > > > 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. > > > > OK, I can do this instead: > > > > DELETE from clp > > where commit_log_id NOT in ( > > SELECT commit_log_id > > FROM clp > > ORDER BY commit_date > > LIMIT 100); > > > > Can you think of a better way? > > -- > > Dan Langille : http://www.langille.org/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > >
On Fri, 28 Feb 2003, Stephan Szabo wrote: > > On Fri, 28 Feb 2003, Dan Langille wrote: > > > Hi folks, > > > > I wanted to delete "old" rows from a table. These are the rows I > > want to keep: > > > > SELECT * > > FROM clp > > ORDER BY commit_date > > LIMIT 100 > > > > 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. > > As long as the inner select returns at least 1 result NOT EXISTS is > going to return false (you haven't correlated the two queries at all). > > > OK, I can do this instead: > > > > DELETE from clp > > where commit_log_id NOT in ( > > SELECT commit_log_id > > FROM clp > > ORDER BY commit_date > > LIMIT 100); > > > > Can you think of a better way? > > Possibly something like: > DELETE FROM clp > WHERE NOT EXISTS ( > select * from (select * from clp order by commit_date limit 100) tmp > where tmp.commit_log_id = clp.commit_log_id > ); > > But I haven't tried it for stupid errors, and am not sure that it'd end up > being any better than NOT IN anyway. Thank you. Here's that plan: Seq Scan on clp (cost=0.00..544.87 rows=50 width=6) (actual time=93.71..763.85 rows=400 loops=1) Filter: (NOT (subplan)) SubPlan -> Subquery Scan tmp (cost=5.37..5.62 rows=100width=12) (actual time=1.51..1.51 rows=0 loops=500) Filter: (commit_log_id = $0) -> Limit (cost=5.37..5.62 rows=100 width=12)(actual time=0.03..1.09 rows=90 loops=500) -> Sort (cost=5.37..5.62 rows=101 width=12) (actual time=0.02..0.38 rows=91 loops=500) Sort Key: commit_date -> Seq Scan on clp (cost=0.00..2.01rows=101 width=12) (actual time=0.23..3.88 rows=500 loops=1)Total runtime: 768.14 msec
On Fri, 28 Feb 2003, Tomasz Myrta wrote: > > Hi folks, > > > > I wanted to delete "old" rows from a table. These are the rows I > > want to keep: > > > > SELECT * > > FROM clp > > ORDER BY commit_date > > LIMIT 100 > > > > 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. > > > > OK, I can do this instead: > > > > DELETE from clp > > where commit_log_id NOT in ( > > SELECT commit_log_id > > FROM clp > > ORDER BY commit_date > > LIMIT 100); > > > > Can you think of a better way? > delete from clp where commit_date < (select commit_date from clp order > by commit_date limit 1 offset 100); Regards, Tomasz Myrta Thank you. That does it well. And it also revealed an error in my original SQL: a mising "desc". Here's the plan FYI: Seq Scan on clp (cost=0.00..2.26 rows=34 width=6) (actual time=8.55..11.92 rows=399 loops=1) Filter: (commit_date < $0) InitPlan -> Limit (cost=5.62..5.62 rows=1 width=8) (actualtime=7.58..7.60 rows=1 loops=1) -> Sort (cost=5.37..5.62 rows=101 width=8) (actual time=6.75..7.17 rows=102 loops=1) Sort Key: commit_date -> Seq Scan on clp (cost=0.00..2.01rows=101 width=8) (actual time=0.26..3.93 rows=500 loops=1)Total runtime: 15.86 msec