pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Meynell <steve@candata.com>
To: Postgresql Mailing List <pgsql-sql@postgresql.org>
Subject: Heres a good one...
Date: Fri, 27 Apr 2001 15:16:26 -0400
Message-ID: <3AE9C58A.2491BF85@candata.com> (raw)

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





view thread (4+ messages)  latest in thread

Message-ID: <3AE9C58A.2491BF85@candata.com>
Permalink:  ../3AE9C58A.2491BF85@candata.com/
Also on:    postgresql.org/message-id/3AE9C58A.2491BF85@candata.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: steve@candata.com
  Subject: Re: Heres a good one...
  In-Reply-To: <3AE9C58A.2491BF85@candata.com>
  References: <3AE9C58A.2491BF85@candata.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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