Re: Another unexpected behaviour

On Wed, Jul 20, 2011 at 9:58 AM, Rob Richardson
<Rob(dot)Richardson(at)rad-con(dot)com>wrote:

> It seems to me that it is inherently wrong to perform any operation on a
> database that depends on the order in which records are retrieved,
> without specifying that order in an ORDER BY clause. The "update t1 set
> f1 = f1 + 1" assumes that the operation will be performed in an order
> that guarantees that the highest unchanged record will be the next
> record processed. I don't believe that any database system should be
> required to support an action like this.
>
> RobR
>

I disagree. I think it depends upon all records being modified before any
are constraint-checked, which may or may not be a reasonable requirement. If
you think of it as a true set operation, it seems like a perfectly
reasonable thing to do ("increment the value of column N in each of the
records of this set"). It seems odd that this should work:

-- drop unique index
-- single update statement
-- apply unique index

But just "single update statement" won't.

--
Rick Genter
rick(dot)genter(at)gmail(dot)com