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