Обсуждение: INSERT / UPDATE into 2 inner joined table simultaneously
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
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 bothtables 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
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
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_idfield 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
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, ChrisSorry, 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
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