Insert multiple rows in a table using a subquery-CodePudding

I'm trying to get all the items of the cart_items and add it to the ticket_items. I've the error "SUBQUERY RETURN MULTIPLE ROWS", but that's what I want, insert all that rows in the ticket_items table in a same query.

INSERT INTO ticket_items (ticket_id, ISBN, quantity) 
VALUES (
    (SELECT COUNT(ticket_id) FROM ticket), 
    (SELECT ISBN FROM cart_items WHERE customer_id = 1),
    (SELECT quantity FROM cart_items WHERE customer_id = 1)
);

CodePudding user response：

The issue is that you are using an aggregate function (COUNT) that will build exactly one value, but both isbn and quantity occur twice or more. So, the insert that will be built by your query is something like

INSERT INTO ticket_items (ticket_id, ISBN, quantity) 
VALUES (10, 1,2,3, 4,5,6);

This is of course not allowed. Since you do a COUNT, you need to apply GROUP BY to create multiple rows which will be inserted. So your insert command will be something like this:

INSERT INTO ticket_items (ticket_id, ISBN, quantity) 
((SELECT COUNT(ticket_id), ISBN, quantity
FROM ticket, cart_items
WHERE customer_id = 1
GROUP BY ISBN, quantity));

This will work correctly and do what you asked for, I created an example here: db<>fiddle

You should check if this is really what you want because for me, it doesn't make sense to insert the count of ticket_id like you describe. But this is something you must check on your own.

CodePudding user response：

Don't use VALUES keyword.

Please see syntax below:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

I would first make sure that select gives you the expected result, then issue the insert statement.

Upon review, I believe the issue is with SELECT part. You should rewrite it as one SELECT:

INSERT INTO ticket_items (ticket_id, ISBN, quantity) 
(SELECT 
(SELECT COUNT(ticket_id) from ticket) as ticket_id, ISBN, quantity
FROM  cart_items
WHERE customer_id = 1)

Good luck!