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[@]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
