Database Administrators Asked by Malks on February 5, 2021
I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table ‘projects’ the column is called ‘id’ and generates every time a new project is inserted. In the table ‘units’ the same id is called ‘projekt_id’.
I now want to add new projects from a third table that is called ‘temp_units’. That table also has information that is relevant for the table ‘units’. So for every row in the ‘temp_units’ table I want to create a new row in the ‘projects’ table and also a new row in the ‘units’ table. Those two rows should be connected through the projects id.
I tried the following query but I get the error: more than one row returned by a subquery used as an expression.
Then I read somewhere about adding Limit = 1, so I added that to the query below (it is shown as a comment there).
With Limit = 1 the code worked but all of the inserted data in the ‘units’ table is connected to the first project created in the ‘projects’ table.
At the moment it is like this:
projects
id
--
1
2
3
Units
Id | projekt_id
---------------
1 | 1
2 | 1
3 | 1
But I need it like this:
projects
id
--
1
2
3
Units
Id | projekt_id
---------------
1 | 1
2 | 2
3 | 3
I am using PostgreSQL 9.5.19. I would really appreciate any help
WITH insert_projekte AS
(
INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
FROM temp_units
RETURNING ID
)
INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
SELECT t.kg_ez, t.ez, (SELECT id from insert_projekte/*LIMIT = 1*/), t.creator_id, t.editor_id, t.inserted_at, t.updated_at
FROM temp_units t
RETURNING ID
So I figured it out eventually
WITH insert_projekte AS
(
INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
FROM temp_units
RETURNING ID, name
)
INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
SELECT t.kg_ez, t.ez, insert_projekte.id, t.creator_id, t.editor_id, t.inserted_at, t.updated_at
FROM temp_units t
JOIN insert_projekte on
insert_projekte.name = t.name
RETURNING ID
I always left the subquery in the second INSERT so it never worked before.
Thank you @CL. for the helpful suggestions.
Answered by Malks on February 5, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP