Thread: Re: order of multiple assignments in UPDATE

While I'm not sure specificly which order they will be determined
in, it has no effect on what the value of "c1" will be. The value 
of "c1" will be 10 minus the value of "c2" where "c2" equals the 
value it was before the update occurred. For exmple...

1. Assume you create the following table...

create table temp
(  c1 int2, c2 int2, c3 int2
);

2. Insert the following row...

insert into temp values (1,1,1);

3. Execute the following update command...

update temp 
set      c1 = (10-c2), c2 = 5, c3 = (10-c2);

4. Execute the following select...

select * from temp;

5. Observe the following output...

c1    c2    c3
==    ==    ==
9    5    9

You will note that both c1 and c3 equal 10 - 1. Neither equals 
10-5. To make the value 10 - {the value after the update}, 
you would need to set the value to c1/c3 equal to the value of 
10 - {the expression used to assign value to c2}. In the 
example above, your update statement would be...

update temp
set      c1 = ( 10 - (5) ), c2 = (5), c3 = ( 10 - (5) );


In article <Pine.LNX.4.21.0104281846010.233-100000@presario>,
anuradha@gnu.org  wrote:

> 
> If I have a query
> 
>   UPDATE tablename SET c1 = 10 - c2, c2 = 4 where ...
> 
> will the two assignments be evaluated from left to right?
> 
> Thanks in advance.
> 
> Anuradha
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

I understand that an UPDATE is done using "old" values of the variables
involved so that we can write

T(n + 1) = U(T(n))

where T(n) and T(n + 1) are the values of the table before and after
update U.

Thanks for all who pointed this out.

Just as a matter of interest, I want to point out that the example you
have given (below) does not _prove_ this. It can imply two situations.

1. The UPDATE query is done using the old values - the actual case.

2. The ordre of assignment statements is _undefined_! If this is the case,  it is possible for the statements to take
placein the order 1, 3, 2  (not 1, 2, 3) which also gives c1, c2, c3 to be 9, 5, 9 respectively.  Becaue initially, the
firstrow reads,
 
  c1 = c2 = c3 = 1
  after first assignment 
  c1 = 10 - c2 = 9
  if third one takes place after this
  c3 = 10 - c2 = 9
  and finally
  c2 = 5

On Sun, 29 Apr 2001, Ossie J. H. Moore wrote:

> While I'm not sure specificly which order they will be determined
> in, it has no effect on what the value of "c1" will be. The value 
> of "c1" will be 10 minus the value of "c2" where "c2" equals the 
> value it was before the update occurred. For exmple...
> 
> 1. Assume you create the following table...
> 
> create table temp
> (
>       c1 int2
>     , c2 int2
>     , c3 int2
> );
> 
> 2. Insert the following row...
> 
> insert into temp values (1,1,1);
> 
> 3. Execute the following update command...
> 
> update temp 
> set      c1 = (10-c2)
>     , c2 = 5
>     , c3 = (10-c2);
> 
> 4. Execute the following select...
> 
> select * from temp;
> 
> 5. Observe the following output...
> 
> c1    c2    c3
> ==    ==    ==
> 9    5    9
> 
> You will note that both c1 and c3 equal 10 - 1. Neither equals 
> 10-5. To make the value 10 - {the value after the update}, 
> you would need to set the value to c1/c3 equal to the value of 
> 10 - {the expression used to assign value to c2}. In the 
> example above, your update statement would be...
> 
> update temp
> set      c1 = ( 10 - (5) )
>     , c2 = (5)
>     , c3 = ( 10 - (5) );
> 
> 
> In article <Pine.LNX.4.21.0104281846010.233-100000@presario>,
> anuradha@gnu.org  wrote:
> 
> > 
> > If I have a query
> > 
> >   UPDATE tablename SET c1 = 10 - c2, c2 = 4 where ...
> > 
> > will the two assignments be evaluated from left to right?
> > 
> > Thanks in advance.
> > 
> > Anuradha
> > 
> >