Re: Another unexpected behaviour

[Rick Genter <rick.genter@xxxxxxxxx>]

On Wed, Jul 20, 2011 at 9:58 AM, Rob Richardson <Rob.Richardson@xxxxxxxxxxx> 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.genter@xxxxxxxxx