Re: Heres a good one...

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
>