Re: Deleting 100 rows which meets certain criteria

On Wed, Dec 30, 2009 at 12:11 PM, 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?
>
> thanks
> Shul
>

You're correct to infer that DELETE does not support LIMIT clauses.
The reason for this is there is no way to tell exactly which rows will
be deleted (unless ORDER BY is used - but there are still other issues
with that as well).

You could, however, do something like such:
DELETE FROM "table" WHERE "column" IN (SELECT "column" FROM "table"
LIMIT 100 OFFSET 0);

(But again, you would need explicit ORDER BY clauses to determine
exactly which rows are actually deleted.