[SQL] order of multiple assignments in UPDATE

[Anuradha Ratnaweera]

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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Re: Heres a good one...

[Anuradha Ratnaweera]

First, posting_date in journal can _NOT_ be of type char(4)! I guess it is
a "date".

Try

update journal set gl_update_flag='Y' from distrib where
journal.gl_update_flag = 'H' and journal.posting_date <= '2001-03-31' and
(journal.objectid = distrib.distrib_objectid or journal.objectid =
distrib.source_objectid)

or

update journal set gl_update_flag='Y' where gl_update_flag = 'H' and
posting_date <= '2001-03-31' and ((objectid in select distrib_objectid
from distrib) or (objectid in select source_objectid from distrib))

On Fri, 27 Apr 2001, Steve Meynell wrote:

> Ok here is what looks like a good one that has stumped me.
> 
> Let me set it up... I have two tables in my database test.  They are
> called journal and distrib.
> 
> journal looks like this (condensed)
> 
>Table "journal"
>Attribute  |  Type  | Modifier
> +-+--
>  objectid   | integer   |
>  posting_date  | char(4)  |
>  gl_update_flag   | char(1)  |
> 
> And distrib look like this (condensed)
> 
> Table "distrib"
> Attribute   |  Type | Modifier
> --+-+--
>  objectid | integer  |
>  distrib_objectid   | integer  |
>  source_objectid   | integer  |
> 
> The dataset for each is as follows
> 
> journal:
> test=# select * from journal;
>  objectid | posting_date | gl_update_flag
> --+--+
>   100| March 31   | H
>   101| March 31   | H
>   102| April 02 | Y
>   103| April 02 | H
>   104| March 14   | H
>   105| February 01  | H
>   106| February 01  | H
> (7 rows)
> 
> distrib:
> test=# select * from distrib;
>  objectid | distrib_objectid | source_objectid
> --+--+-
> 1  |103 | 100
> 2  |104 | 100
> 3  |101 | 102
> 4  |101 | 105
> (4 rows)
> 
> Now the trick here is...
> 
> I want to update the gl_update_flag in journal to Y all of the records
> where the gl_update_flag is H now and the posting date is before or on
> March 31 and where the objectid from journal matches either the
> distrib_objectid or the source_objectid from the distrib table we need
> to also update the opposite journal entry to Y as well.  And can this be
> done in one command?
> 
> An example from the above data set would be  Journal objectid 100 would
> change and so would 103 and 104.
> And Journal objectid 101 would update 101 and 102 and 105.
> And Journal objectid 106 would only update 106.
> 
> Any Ideas?
> 
> Thanks in Advance,
> Steve
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: order of multiple assignments in UPDATE

[Ossie J. H. Moore]

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 ,
[EMAIL PROTECTED]  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 [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly