How to use a WITH query to insert into a table and insert the returning id into another table?

Question

I'm trying to insert a row into a table named table1 if only 'value' exists in table2. The newly created row returns an id and I want to use this id through a WITH query and insert it into a third table named table3.

Here is the sql statement I used.

WITH new_key AS (
    INSERT INTO table1(name, value) 
    SELECT 'test', value
        FROM table2 
    WHERE value = 'some_value'
    RETURNING id
)
INSERT INTO table3(table1_id) 
VALUES (new_key);

The INSERT query in the WITH query works and returns an id. Unfortunately the whole statement returns "new_key column does not exist".

an INSERT don't return "values" .. you need a insert ..select — ScaisEdge
– ScaisEdge, Commented Apr 21, 2019 at 19:01

Marth · Accepted Answer · 2019-04-21 19:01:22Z

1

You need to select the value from the CTE:

WITH new_key AS (
    INSERT INTO table1(name, value) 
    SELECT 'test', value
        FROM table2 
    WHERE value = 'some_value'
    RETURNING id
)
INSERT INTO table3(table1_id) 
SELECT id
FROM new_key;

answered Apr 21, 2019 at 19:01

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use a WITH query to insert into a table and insert the returning id into another table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related