pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT / UPDATE into 2 inner joined table simultaneously
6+ messages / 4 participants
[nested] [flat]
* INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-06 19:59 Lou <lou@dayspringpublisher.com>
0 siblings, 2 replies; 6+ messages in thread
From: Lou @ 2019-03-06 19:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi everyone,
This is my first post here.
I have two tables named c and p. When using SELECT, they are linked
using an INNER JOIN like in this example: "SELECT * FROM c INNER JOIN p
ON c.id = p.c_id WHERE name = 'Jones';"
How can I INSERT new rows into both tables simultaneously with
automatically created id numbers, and how can I UPDATE both tables
simultaneously?
Lou
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-06 20:11 Christopher Swingley <cswingle@swingleydev.com>
parent: Lou <lou@dayspringpublisher.com>
1 sibling, 1 reply; 6+ messages in thread
From: Christopher Swingley @ 2019-03-06 20:11 UTC (permalink / raw)
To: Lou <lou@dayspringpublisher.com>; +Cc: pgsql-sql@lists.postgresql.org
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
--
Christopher Swingley
Fairbanks, Alaska
http://swingleydev.com/
cswingle@swingleydev.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-06 21:36 Lou <lou@dayspringpublisher.com>
parent: Christopher Swingley <cswingle@swingleydev.com>
0 siblings, 2 replies; 6+ messages in thread
From: Lou @ 2019-03-06 21:36 UTC (permalink / raw)
To: Christopher Swingley <cswingle@swingleydev.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-06 21:41 Christopher Swingley <cswingle@swingleydev.com>
parent: Lou <lou@dayspringpublisher.com>
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Swingley @ 2019-03-06 21:41 UTC (permalink / raw)
To: Lou <lou@dayspringpublisher.com>; +Cc: pgsql-sql@lists.postgresql.org
Lou,
On Wed, Mar 6, 2019 at 12:36 PM Lou <lou@dayspringpublisher.com> wrote:
> 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.
I think what you want to do is use a transaction (BEGIN), insert the
data into table c RETURNING id, then insert the data into table p
using the id returned from the first query. If there were no errors,
COMMIT the transaction, otherwise ROLLBACK. It's two queries, but
because of the transaction, it's happening atomically within the
database.
Cheers,
Chris
--
Christopher Swingley
Fairbanks, Alaska
http://swingleydev.com/
cswingle@swingleydev.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-06 22:45 Steve Midgley <science@misuse.org>
parent: Lou <lou@dayspringpublisher.com>
1 sibling, 0 replies; 6+ messages in thread
From: Steve Midgley @ 2019-03-06 22:45 UTC (permalink / raw)
To: Lou <lou@dayspringpublisher.com>; +Cc: Christopher Swingley <cswingle@swingleydev.com>; pgsql-sql@lists.postgresql.org
On Wed, Mar 6, 2019 at 1:36 PM Lou <lou@dayspringpublisher.com> wrote:
> 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> <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
>
Lou, I think what you want is to insert into both tables in a single "atomic
<https://en.wikipedia.org/wiki/Atomicity_(database_systems)>"; transaction.
Basically you just issue to insert statements, sandwiched between a begin
<https://www.postgresql.org/docs/current/sql-begin.html; / commit
<https://www.postgresql.org/docs/current/sql-commit.html>transaction block.
If you are unhappy with the way your inserts are working, you can rollback
the transaction instead of committing. Everyone who is trying to read the
database will experience the data in the two tables as either "both
changed" or "neither changed."
So to copy the data - you can insert into the c table, and then reuse the
c_id in the table p, to maintain relational integrity.
Apologies if I've misunderstood your question, and given you too basic an
answer.
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT / UPDATE into 2 inner joined table simultaneously
@ 2019-03-07 05:44 Tony Shelver <tshelver@gmail.com>
parent: Lou <lou@dayspringpublisher.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tony Shelver @ 2019-03-07 05:44 UTC (permalink / raw)
To: Lou <lou@dayspringpublisher.com>; +Cc: pgsql-sql@lists.postgresql.org
Normally I would not recommend trying to update / insert / delete 2 joined
tables simultaneously, but.... if you have a real business or technical
reason to do this, alos look at 'INSTEAD OF' triggers on views, where the
view would contain your joined SQL statement. The instead of trigger can
then contain whatever SQL your heart desires for update / insert / delete
functionality..
Just another option to the CTE mentioned before, and allows some complex
logic to be implemented.
In a past life we had a database where we stored most real life data
objects (people, organizations, systems, programs and so on) in generic
object tables where the objects were logically related to each other in
several different ways. Due to constant and unforeseeable changes in
relationships between data objects and object types and structures, we
created a generic structure that stored each object in the same table /
tables with a base set of columns and then a set of generic columns and an
overflow table for additional data elements (JSON, where were you?...).
This method also hid the complexity of date-tracking changes to each object
and relationship from the developer.
We ended up with a database with tens of tables, instead of hundreds.
To cut a long story short, we used views to flatten this ll out and to
represent the individual object types. We gave these views CRUD
functionality using 'INSTEAD OF' triggers,that were coded up and tested
by the DB dev team, and then distributed to the development team, who
thought they were working with simple table structures.
As a side benefit, this was a large, multi-million dollar project in a well
known US financial institution with very rigorous devops procedures around
database structure changes, that could take a week or two to request and
change ANY table or column structure in the dev / test DBs, and even
longer to move into prod. Meeting project delivery timelines would have
been dead in the water if we had used a traditional fully normalized
structure due to constant changes as we brought new systems and departments
into the application (identity and access management). For some reason,
stored procedures and views could slip by under the DB approval process
radar screen as they were regarded as app dev objects.
Design was well proven in their production environment, and we took that
same design forward into several other large clients and implemented the
entire code base with no DB design or structure changes, just configuration
of the master data tables driving it.
On Wed, 6 Mar 2019 at 21:59, Lou <lou@dayspringpublisher.com> wrote:
> Hi everyone,
>
> This is my first post here.
>
> I have two tables named c and p. When using SELECT, they are linked using
> an INNER JOIN like in this example: "SELECT * FROM c INNER JOIN p ON c.id
> = p.c_id WHERE name = 'Jones';"
>
> How can I INSERT new rows into both tables simultaneously with
> automatically created id numbers, and how can I UPDATE both tables
> simultaneously?
>
> Lou
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-03-07 05:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-06 19:59 INSERT / UPDATE into 2 inner joined table simultaneously Lou <lou@dayspringpublisher.com>
2019-03-06 20:11 ` Christopher Swingley <cswingle@swingleydev.com>
2019-03-06 21:36 ` Lou <lou@dayspringpublisher.com>
2019-03-06 21:41 ` Christopher Swingley <cswingle@swingleydev.com>
2019-03-06 22:45 ` Steve Midgley <science@misuse.org>
2019-03-07 05:44 ` Tony Shelver <tshelver@gmail.com>
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