Re: using limit with delete

Hi Chris,

Just a thought: if you have to clear the table anyway, wouldn't it work
for you to use truncate ? That should be faster than delete.

HTH,
Csaba.

On Thu, 2005-04-07 at 04:11, Chris Smith wrote:
> I don't care about the order in my particular case, just that I have to
> clear the table.
>
> I'll try the subquery and see how I go :)
>
> Thanks!
>
> 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 ...);
> >
> > -Neil
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >