Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: column update order

Re: column update order

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 21 Nov 2006 18:26:58 +0100
Message-ID: <456336e2$0$7748$426a74cc@news.free.fr>

<geraint.williams_at_gmail.com> a écrit dans le message de news: 1164129607.264559.104720_at_f16g2000cwb.googlegroups.com...
|
| Robert Klemme wrote:
| > 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
|
| Yes, that's what I really wanted to know.
|
| Thanks.
|
| (Apologies for confusing the issue by using a reserved word in my
| example)
|

I don't read the whole thread (apologies if this was already answered) but the answer is, the column order does not matter, you always get the values at the beginning of the statement in the right members.

SQL> select * from t4;
O_DATE C_DATE
---------- ----------

19/07/2005 21/03/2006
29/07/2005 26/03/2006
08/08/2005 31/03/2006
18/08/2005 05/04/2006
28/08/2005 10/04/2006
07/09/2005 15/04/2006
17/09/2005 20/04/2006
27/09/2005 25/04/2006
07/10/2005 30/04/2006
17/10/2005 05/05/2006

10 rows selected.

SQL> update t4 set c_date=trunc(sysdate), o_date=c_date;

10 rows updated.

SQL> select * from t4;
O_DATE C_DATE
---------- ----------

21/03/2006 21/11/2006
26/03/2006 21/11/2006
31/03/2006 21/11/2006
05/04/2006 21/11/2006
10/04/2006 21/11/2006
15/04/2006 21/11/2006
20/04/2006 21/11/2006
25/04/2006 21/11/2006
30/04/2006 21/11/2006
05/05/2006 21/11/2006

10 rows selected.

Regards
Michel Cadot Received on Tue Nov 21 2006 - 11:26:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US