CODEWITHSUNDEEP
sqlpostgresql
triou
triou

Reputation: 47

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

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".

Upvotes: 0

Views: 40

Answers (1)

Marth
Marth

Reputation: 24812

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;

Upvotes: 1

Related Questions