Re: column update order

Robert Klemme wrote:
> On 21.11.2006 15:31, Charles Hooper wrote:
> > Note: Transaction Isolation Levels can be altered at the session level.
> > The default transaction isolation level does not enforce that SELECTs,
> > UPDATEs, INSERTs, and DELETEs will be consistent as of the start of the
> > transaction. If a column value is updated and committed in another
> > session, the changed values will be visible in the second session. For
> > example:
>
> From all that I can tell this thread is not about multi session
> consistency but about column values referenced in an UPDATE statement.
> The original question was
>
> <quote>
> Is the following safe, can we rely on the order of the columns being
> updated? (Oracle 10g)
>
> update t set old_date = current_date, current_date = sysdate;
> </quote>
>
> I guess what the OP really wanted to know was whether it is safe to
> refer to a column value in an UPDATE statement in a right hand side
> expression and always get the /old/ value - even if the same column is
> referred to in a left hand side expression. And for all I know it is.
>
> Kind regards
>
> robert

You are absolutely correct. I was trying to correct an error in a statement that I made in a previous post of this thread, where I indicated that Oracle ensures read consistency within a transaction - it will, but not by default.

CREATE TABLE T4 (
  O_DATE DATE,
  C_DATE DATE); INSERT INTO
  T4
SELECT
  TRUNC(SYSDATE-10*(50-ROWNUM)) OLD_DATE,   TRUNC(SYSDATE-5*(50-ROWNUM)) CURRENT_DATE FROM
  DBA_OBJECTS
WHERE
  ROWNUM <=10;

COMMIT; Taking a look at the above, is it possible to confuse Oracle and have it not assign a previously existing value? The following update statement attempts to set the O_DATE to the value from the previous row, when sorted by O_DATE, and set the C_DATE to the value from the next row, when sorted by O_DATE.
UPDATE
  T4
SET
  (O_DATE,C_DATE)=
    (SELECT

      LAG(O_DATE,1) OVER (ORDER BY O_DATE),
      LEAD(C_DATE,1) OVER (ORDER BY O_DATE)
    FROM
      T4 X
    WHERE
      T4.O_DATE=X.O_DATE);

The results:
       O_DATE		    C_DATE
==================== ====================

10 ROWS SELECTED Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 21 2006 - 12:23:08 CST