Re: column update order

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 21 Nov 2006 13:10:18 +0100
Message-ID: <4sg8l9Fv4ge3U1@mid.individual.net>

On 21.11.2006 04:59, Charles Hooper wrote:
> 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.

Frankly, I don't see the difference between the two examples other than the column names. Are you sure that the first output was not caused by naming the column CURRENT_DATE?

SQL> select current_date from dual;

CURRENT_

---

21.11.06

SQL> @ C:\Temp\update.sql

CURRENT_

---

21.11.06

Tabelle wurde erstellt.

10 Zeilen wurden erstellt.

Transaktion mit COMMIT abgeschlossen.

    ROW_SEQ OLD_DATE CURRENT_
---------- -------- --------

          1 19.07.05 21.03.06
          2 29.07.05 26.03.06
          3 08.08.05 31.03.06
          4 18.08.05 05.04.06
          5 28.08.05 10.04.06
          6 07.09.05 15.04.06
          7 17.09.05 20.04.06
          8 27.09.05 25.04.06
          9 07.10.05 30.04.06
         10 17.10.05 05.05.06

10 Zeilen ausgewählt.

10 Zeilen wurden aktualisiert.

Transaktion mit COMMIT abgeschlossen.

    ROW_SEQ OLD_DATE CURRENT_
---------- -------- --------

          1 21.03.06 21.11.06
          2 26.03.06 21.11.06
          3 31.03.06 21.11.06
          4 05.04.06 21.11.06
          5 10.04.06 21.11.06
          6 15.04.06 21.11.06
          7 20.04.06 21.11.06
          8 25.04.06 21.11.06
          9 30.04.06 21.11.06
         10 05.05.06 21.11.06

10 Zeilen ausgewählt.

Tabelle wurde gelöscht.

Kind regards

        robert

SELECT CURRENT_DATE FROM DUAL; CREATE TABLE T4 (
  ROW_SEQ NUMBER,
  OLD_DATE DATE,
  CURRENT_DATE DATE); INSERT INTO
  T4
SELECT
  ROWNUM,
  TRUNC(SYSDATE-10*(50-ROWNUM)) c1,
  TRUNC(SYSDATE-5*(50-ROWNUM)) c2
FROM
  AG_URL
WHERE
  ROWNUM <=10;

COMMIT; SELECT *
FROM T4
ORDER BY ROW_SEQ; UPDATE
  T4
SET
  OLD_DATE=T4.CURRENT_DATE,
  T4.CURRENT_DATE=TRUNC(SYSDATE); COMMIT; SELECT *
FROM T4
ORDER BY ROW_SEQ; DROP TABLE T4; Received on Tue Nov 21 2006 - 06:10:18 CST