Thread: Heres a good one...

Heres a good one...

From
Steve Meynell
Date:
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    |
April02         | 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




Re: Heres a good one...

From
Anuradha Ratnaweera
Date:
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
> 



Re: Heres a good one...

From
Steve Meynell
Date:
First,  thank you very much for trying but you were a bit off the mark but
close.  Second, posting_date being of type char(4) is quite legal and
irrelevant for this exercise but for argument sake lets say char(15) but
apart from all that...

Your sql statement was close but it only update 4 out of the possible 6 that
is should have updated...  journal.objectid# 103 should have been updated
because of journal.objectid# 100 was and journal.objectid# 106 should have
been because it alone met the before March 31 and update flag = H criteria.
But it is a start for me thanks.

And your second sql statement just didn't work.  It said 'ERROR:  parser:
parse error at or near "select"'

Steve


Anuradha Ratnaweera wrote:

> 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))
>



Re: Heres a good one...

From
Anuradha Ratnaweera
Date:
I just wanted to show the possibility of trying your problem using 

1. Update using another table

2. Use of subselects

I forgot to add parantheses around the two subselect statements. The
following should be okey.

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)))

Can you comment on the speed of this. I sometimes found it to be slow.

Also, can anybody tell me if the two statements can be redeced to a single
"canonical" form?

Anuradha

On Mon, 30 Apr 2001, Steve Meynell wrote:

> First,  thank you very much for trying but you were a bit off the mark but
> close.  Second, posting_date being of type char(4) is quite legal and
> irrelevant for this exercise but for argument sake lets say char(15) but
> apart from all that...
> 
> Your sql statement was close but it only update 4 out of the possible 6 that
> is should have updated...  journal.objectid# 103 should have been updated
> because of journal.objectid# 100 was and journal.objectid# 106 should have
> been because it alone met the before March 31 and update flag = H criteria.
> But it is a start for me thanks.
> 
> And your second sql statement just didn't work.  It said 'ERROR:  parser:
> parse error at or near "select"'
> 
> Steve
> 
> 
> Anuradha Ratnaweera wrote:
> 
> > 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))
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>