Re: allow LIMIT in UPDATE and DELETE

On Fri, 2006-05-19 at 17:43, SCassidy(at)overlandstorage(dot)com wrote:
> Regarding Oracle's ROWNUM - since they have to use that instead of OFFSET
> and LIMIT, that isn't much of an argument for the Oracle way. When
> converting queries into Oracle SQL, I always _really_ miss OFFSET and
> LIMIT. They are much easier to use than ROWNUM, especially with ORDER BY.

I would agree here, but for my purpose would have been a lot easier to
parameterize the limit syntax only than to completely rewrite the query
for postgres.

> I think that more databases support OFFSET and LIMIT than ROWNUM (the
> Oracle way).
I actually don't care what's the syntax as long I can create a query
with the syntactically equivalent parts in the same place of the query
and the same number and order of parameters. Then it's fairly easy to
parameterize it and have the same code handle it... otherwise I have to
write special code for each data base. Sometimes I have to do that
anyway for different reasons, but it is a PITA to maintain it, so I try
to minimize the number of places where I have to do it.

> Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one
> time I did something similar in Oracle, I used partitions, and just dropped
> or truncated the partition containing the old data.

Perfectly fine, I also don't use many of the features available :-) That
doesn't mean it is not useful for others...

Cheers,
Csaba.