TransWikia.com

Problem Insert Data to connected tables postgresql

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

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP