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?
delete from clp where commit_date < (select commit_date from clp order by
commit_date limit 1 offset 100);
Regards,
Tomasz Myrta

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(at)langille(dot)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(at)langille(dot)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 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.

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, 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) (actual time=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.01 rows=101 width=8)
(actual time=0.26..3.93 rows=500 loops=1)
Total runtime: 15.86 msec

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=100 width=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.01 rows=101
width=12) (actual time=0.23..3.88 rows=500 loops=1)
Total runtime: 768.14 msec

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) (actual time=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.01 rows=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
>
>
>
>

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?