pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
order of multiple assignments in UPDATE
3+ messages / 2 participants
[nested] [flat]

* order of multiple assignments in UPDATE
@ 2001-04-28 12:48 Anuradha Ratnaweera <anuradha@gnu.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Anuradha Ratnaweera @ 2001-04-28 12:48 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: order of multiple assignments in UPDATE
@ 2001-04-29 05:30 Ossie J. H. Moore <ossie.moore@home.com>
  2001-05-01 19:46 ` Re: order of multiple assignments in UPDATE Anuradha Ratnaweera <anuradha@gnu.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Ossie J. H. Moore @ 2001-04-29 05:30 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: order of multiple assignments in UPDATE
  2001-04-29 05:30 Re: order of multiple assignments in UPDATE Ossie J. H. Moore <ossie.moore@home.com>
@ 2001-05-01 19:46 ` Anuradha Ratnaweera <anuradha@gnu.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Anuradha Ratnaweera @ 2001-05-01 19:46 UTC (permalink / raw)
  To: Ossie J. H. Moore <ossie.moore@home.com>; +Cc: 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 place in the order 1, 3, 2
   (not 1, 2, 3) which also gives c1, c2, c3 to be 9, 5, 9 respectively.
   Becaue initially, the first row 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
> > 
> > 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-01 19:46 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-28 12:48 order of multiple assignments in UPDATE Anuradha Ratnaweera <anuradha@gnu.org>
2001-04-29 05:30 Re: order of multiple assignments in UPDATE Ossie J. H. Moore <ossie.moore@home.com>
2001-05-01 19:46 ` Anuradha Ratnaweera <anuradha@gnu.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox