INSERT / UPDATE into 2 inner joined table simultaneously

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Lou
Reply | Threaded
Open this post in threaded view
|

INSERT / UPDATE into 2 inner joined table simultaneously

Lou

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

Reply | Threaded
Open this post in threaded view
|

Re: INSERT / UPDATE into 2 inner joined table simultaneously

Christopher Swingley
Lou,

On Wed, Mar 6, 2019 at 10:59 AM Lou <[hidden email]> 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/
[hidden email]

Lou
Reply | Threaded
Open this post in threaded view
|

Re: INSERT / UPDATE into 2 inner joined table simultaneously

Lou
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 [hidden email] 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



Reply | Threaded
Open this post in threaded view
|

Re: INSERT / UPDATE into 2 inner joined table simultaneously

Christopher Swingley
Lou,

On Wed, Mar 6, 2019 at 12:36 PM Lou <[hidden email]> 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/
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: INSERT / UPDATE into 2 inner joined table simultaneously

Steve Midgley-3
In reply to this post by Lou


On Wed, Mar 6, 2019 at 1:36 PM Lou <[hidden email]> 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 [hidden email] 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" transaction. Basically you just issue to insert statements, sandwiched between a begin / commit 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



Reply | Threaded
Open this post in threaded view
|

Re: INSERT / UPDATE into 2 inner joined table simultaneously

Tony Shelver
In reply to this post by Lou
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 <[hidden email]> 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