Re: Deleting 100 rows which meets certain criteria
От | Andreas Kretschmer |
---|---|
Тема | Re: Deleting 100 rows which meets certain criteria |
Дата | |
Msg-id | 20091230184827.GA932@tux обсуждение исходный текст |
Ответ на | Deleting 100 rows which meets certain criteria (shulkae <shulkae@gmail.com>) |
Список | pgsql-general |
shulkae <shulkae@gmail.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°
В списке pgsql-general по дате отправления: