Re: Heres a good one... - Mailing list pgsql-sql

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
>