How to Insert into table using multiple row returned value from subquery?

Question

How exactly do I get this to work? I am trying my best to form a query that takes the primary keys generated from the first query and then inserts them into the 2nd table along with a static 2nd value(33). I am obviously getting a "more than one row returned by a subquery used as an expression" error. I'm googled my eye balls out and can't figure out this issue. Maybe there's a better way to do what I am trying to do.

I am using Postgresql 9.5 if that matters.

 WITH x AS (INSERT INTO OPTIONS (manufacturer_id, category, name, description)
VALUES (
    UNNEST(ARRAY['10', '22', '33']),
    'ExtColor',
    UNNEST(ARRAY['EC', 'IC', 'IO']),
    UNNEST(ARRAY['a', 'b', 'c'])
)
RETURNING option_id)
INSERT INTO opt_car_data (car_id, option_id) VALUES ((SELECT option_id FROM x), 33);

klin · Accepted Answer · 2016-10-31 04:16:29Z

1

WITH x AS (
    INSERT INTO options (manufacturer_id, category, name, description)
    VALUES (
        UNNEST(ARRAY['10', '22', '33']),
        'ExtColor',
        UNNEST(ARRAY['EC', 'IC', 'IO']),
        UNNEST(ARRAY['a', 'b', 'c'])
    )
    RETURNING option_id
)
INSERT INTO opt_car_data (car_id, option_id) 
SELECT option_id, 33 
FROM x;

answered Oct 31, 2016 at 4:16

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

How to Insert into table using multiple row returned value from subquery?

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