Re: order of multiple assignments in UPDATE - Mailing list pgsql-sql

From Anuradha Ratnaweera
Subject Re: order of multiple assignments in UPDATE
Date
Msg-id Pine.LNX.4.21.0105020134530.533-100000@presario
Whole thread Raw
In response to Re: order of multiple assignments in UPDATE  ("Ossie J. H. Moore" <ossie.moore@home.com>)
List pgsql-sql
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
> > 
> > 



pgsql-sql by date:

Previous
From: "Gerald Gutierrez"
Date:
Subject: INSERT slowdown ...
Next
From: Wilkinson Charlie E
Date:
Subject: RE: List Concatination [warning]