pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorder 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