Re: Deleting 100 rows which meets certain criteria

shulkae <shulkae(at)gmail(dot)com> wrote:

> I am newbie to postgres/SQL.
>
> I want to delete all rows exceeding 400 hours (10 days) and I want to
> limit deletion of only 100 records at a time.
>
> I was trying the following in PostgreSQL:
>
> DELETE from mytable WHERE (now() - timestamp_field > INTERVAL '400
> hour' ) LIMIT 100;
>
> Looks like DELETE syntax doesn't support LIMIT.
>
> Is there any other way to achieve this?

Select all rows with this condition, order by this field descending, limit 100.

Now you have all possible rows to delete. Simple example:

test=*# select * from shulkae ;
i
----
2
4
1
6
4
1
9
11
13
2
17
15
(12 rows)

Time: 0.211 ms
test=*# delete from shulkae where i in (select * from shulkae where i > 10 order by i desc limit 2);
DELETE 2
Time: 0.542 ms
test=*# select * from shulkae ;
i
----
2
4
1
6
4
1
9
11
13
2
(10 rows)

Time: 0.200 ms
test=*#

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°