Subject: Re: INSERT / UPDATE into 2 inner joined table simultaneously
To: Christopher Swingley <cswingle@swingleydev.com>
Cc: pgsql-sql@lists.postgresql.org
References: <38a1b75c-2a29-5833-cbd6-6b76dca019da@dayspringpublisher.com>
 <CAHsw449uo18j9fSxd-KZgwWSSf3nTqe2v6ho86U7OGNXFC4S2g@mail.gmail.com>
From: Lou <lou@dayspringpublisher.com>
Message-ID: <cd018283-810e-9533-60b0-ac81dbd94840@dayspringpublisher.com>
Date: Wed, 6 Mar 2019 15:36:21 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.1
MIME-Version: 1.0
In-Reply-To: 
 <CAHsw449uo18j9fSxd-KZgwWSSf3nTqe2v6ho86U7OGNXFC4S2g@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------2134BD995E0B91FA974D4FAA"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------2134BD995E0B91FA974D4FAA
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hi Chris,

Thank you for answering so quickly.

On 3/6/19 2:11 PM, Christopher Swingley wrote:
> Lou,
>
> On Wed, Mar 6, 2019 at 10:59 AM Lou <lou@dayspringpublisher.com> wrote:
>> How can I INSERT new rows into both tables simultaneously with automatically created id numbers, and how can I UPDATE both tables simultaneously?
> Although I have no idea why you would want to do this, you can insert
> data into two tables with one query using a common table expression:
>
> WITH cinsert AS (
>     INSERT INTO c (id, name) VALUES (1, 'Jones')
>     RETURNING id, name)
> INSERT INTO p (id, name) (SELECT * FROM cinsert);
>
> Cheers,
>
> Chris

Sorry, I did not clearly explain what I'm trying to do. The two tables
contain different data. The c table contains company data, and the p
table contains personal data about my contact person in that company.
The only data the two tables share is the contents of c.id which must be
inserted into the p.c_id field (so that the two tables can later be
inner joined by SELECT). I've programmed a data entry screen which shows
the fields of both tables together, so that the data for both tables can
be inserted or edited in one sitting. The data for both tables needs to
be saved at the same time so that the id number of table c can be copied
into the c_id field of table p.

Lou


--------------2134BD995E0B91FA974D4FAA
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">Hi Chris,</div>
    <div class="moz-cite-prefix"><br>
    </div>
    <div class="moz-cite-prefix">Thank you for answering so quickly.<br>
    </div>
    <div class="moz-cite-prefix"><br>
    </div>
    <div class="moz-cite-prefix">On 3/6/19 2:11 PM, Christopher Swingley
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHsw449uo18j9fSxd-KZgwWSSf3nTqe2v6ho86U7OGNXFC4S2g@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">Lou,

On Wed, Mar 6, 2019 at 10:59 AM Lou <a class="moz-txt-link-rfc2396E" href="mailto:lou@dayspringpublisher.com">&lt;lou@dayspringpublisher.com&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">How can I INSERT new rows into both tables simultaneously with automatically created id numbers, and how can I UPDATE both tables simultaneously?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Although I have no idea why you would want to do this, you can insert
data into two tables with one query using a common table expression:

WITH cinsert AS (
    INSERT INTO c (id, name) VALUES (1, 'Jones')
    RETURNING id, name)
INSERT INTO p (id, name) (SELECT * FROM cinsert);

Cheers,

Chris
</pre>
    </blockquote>
    <p>Sorry, I did not clearly explain what I'm trying to do. The two
      tables contain different data. The c table contains company data,
      and the p table contains personal data about my contact person in
      that company. The only data the two tables share is the contents
      of c.id which must be inserted into the p.c_id field (so that the
      two tables can later be inner joined by SELECT). I've programmed a
      data entry screen which shows the fields of both tables together,
      so that the data for both tables can be inserted or edited in one
      sitting. The data for both tables needs to be saved at the same
      time so that the id number of table c can be copied into the c_id
      field of table p. <br>
    </p>
    <p>Lou<br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
  </body>
</html>

--------------2134BD995E0B91FA974D4FAA--