Re: order of row processing affects updates

On Sun, 19 Sep 2004 08:37:10 -0500, Paramveer.Singh wrote:

> Hi all!
> consider the following table
>
> table a (id int primary key)
> and a particular instance of it:
>       id
> ------------------------
>       5
>       6
>
> now update a set id = id +1;
> fails if the executor processes row with 5 first.
> This means that the query will succeed sometimes and fail at other times
> (when it processes 6 first).
> Are there any workarounds to this?
> Basically what we would like to do would be something like doing constraint
> validations only at the end of execution of an update query, instead of
> after every row update.
>
> to see why sometimes this query might work, run the following commands:
>
> create table a (id int primary key);
> insert into a values (6);
> insert into a values (5);
> update a set id = id +1;
>
> basically we would like to see uniformity in execution. Either the query
> should always fail or always succeed.
> Are there any standards on this?
>

What is the logic for changing the PK?

One option is a SELECT INTO statement.  The id can be incremented during
the statement.