Re: column update order

Robert Klemme wrote:
> On 21.11.2006 04:59, Charles Hooper wrote:
> > 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;
Good catch. I noticed that you appended the table name in front of CURRENT_DATE, while the OP did not. I suspect that the OP may have overlooked the possibility of something else was happening in the system.

Interestingly, when I performed the above test from home connecting into a 10.2.0.2 database with a 8.1.7 client, the OLD_DATE column was set to SYSDATE + 5/24, while in the office connecting into a 10.2.0.2 database with a 10.2.0 client, OLD_DATE was set to SYSDATE. I guess consistent has more than one definition.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 21 2006 - 06:28:29 CST