Re: column update order

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Nov 2006 19:59:04 -0800
Message-ID: <1164081544.328801.213530@h48g2000cwc.googlegroups.com>

geraint.williams_at_gmail.com wrote:
> Hello,
>
> 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;
>
>
> Thanks

Let's try a test:
CREATE TABLE T4 (
  OLD_DATE DATE,
  CURRENT_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; SELECT
*
FROM
T4;

OLD_DATE CURRENT_DATE

18-JUL-2005    20-MAR-2006
28-JUL-2005    25-MAR-2006
07-AUG-2005  30-MAR-2006
17-AUG-2005   04-APR-2006
27-AUG-2005   09-APR-2006
06-SEP-2005   14-APR-2006
16-SEP-2005   19-APR-2006
26-SEP-2005   24-APR-2006
06-OCT-2005   29-APR-2006
16-OCT-2005   04-MAY-2006

UPDATE
  T4
SET
  OLD_DATE=CURRENT_DATE,
  CURRENT_DATE=TRUNC(SYSDATE); 10 ROWS UPDATED SELECT
  *
FROM
  T4;

OLD_DATE                 CURRENT_DATE
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00

21-NOV-2006 03:41:45 20-NOV-2006 00:00:00 21-NOV-2006 03:41:45 20-NOV-2006 00:00:00 That came out a bit different than expected. Wonder what happened?

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; SELECT
*
FROM
T4;

OLD_DATE CURRENT_DATE

18-JUL-2005    20-MAR-2006
28-JUL-2005    25-MAR-2006
07-AUG-2005  30-MAR-2006
17-AUG-2005   04-APR-2006
27-AUG-2005   09-APR-2006
06-SEP-2005   14-APR-2006
16-SEP-2005   19-APR-2006
26-SEP-2005   24-APR-2006
06-OCT-2005   29-APR-2006
16-OCT-2005   04-MAY-2006

UPDATE
  T4
SET
  O_DATE=C_DATE,
  C_DATE=TRUNC(SYSDATE); 10 ROWS UPDATED SELECT
  *
FROM
  T4;

O_DATE C_DATE

20-MAR-2006    20-NOV-2006
25-MAR-2006    20-NOV-2006
30-MAR-2006    20-NOV-2006
04-APR-2006     20-NOV-2006
09-APR-2006     20-NOV-2006
14-APR-2006     20-NOV-2006
19-APR-2006     20-NOV-2006
24-APR-2006     20-NOV-2006
29-APR-2006     20-NOV-2006
04-MAY-2006     20-NOV-2006

That was expected.

An individual UPDATE, INSERT, or DELETE will be consistent as of the time the DDL begins, as will a SELECT. Additionally, within a transaction, all SELECTs, UPDATEs, INSERTs, and DELETEs will be consistent as of the start of the transaction.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 20 2006 - 21:59:04 CST