Re: using limit with delete

On Thu, Apr 07, 2005 at 12:02:24PM +1000, Neil Conway wrote:
> Chris Smith wrote:
> >I'm trying to use a limit clause with delete, but it doesn't work at the
> >moment
>
> It isn't in the SQL standard, and it would have undefined behavior: the
> sort order of a result set without ORDER BY is unspecified, so you would
> have no way to predict which rows DELETE would remove.
>
> >delete from table where x='1' limit 1000;
>
> You could use a subquery to achieve this:
>
> DELETE FROM table WHERE x IN
> (SELECT x FROM table ... ORDER BY ... LIMIT ...);

In particular, x can be the system column "ctid", a trick which could be
useful to remove duplicates, for example.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)