Stored procedure to Insert data into table with select statement and argument parameter

Question

I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.

I have a table with three columns (cart_id, product, sold_quantity). Now, I want to populate this table with a stored procedure. In this table, cart_id will be passed as stored procedure argument while other two columns are populated from another table. I want to insert latest 10 rows from other table and for all these rows, the cart_id will be same

First I tried,

BEGIN
insert into my_table (cart_id, product, sold_quantity) VALUES (cart_id, (Select product, sold_quantity from other_table limit 10))
END

Here, cart_id is passed in procedure argument. This obviously does not work as the coulmn count does not match. So, I tried,

BEGIN
insert into my_table (cart_id, product, sold_quantity) VALUES (cart_id, (Select product from other_table limit 10), (Select  sold_quantity from other_table limit 10))
END

This returns error Subquery returns more than 1 row

Is there any way I can populate my table with data from a combination of argument parameter and other table?

score 1 · Accepted Answer · answered Mar 15 '23 at 09:50

The problem is the VALUES in your particular case. Use INSERT INTO SELECT.

Query.

insert into my_table (cart_id, 
                      product, 
                      sold_quantity
                      ) 
select  cart_id, 
        product,
        sold_quantity
from other_table limit 10 ;

Example Consider the following data

create table other_table(
    product int ,
    sold_quantity int );
insert into other_table values  (1,10), (2,10), (3,10), (4,10), (5,10), 
(1,10), (2,10), (3,10), (4,10), (5,10);
create table my_table(
    cart_id int,
    product int(9) ,
    sold_quantity int );

Procedure

CREATE PROCEDURE insert_into_table(cart_id int )
BEGIN       
   insert into my_table (cart_id, 
                      product, 
                      sold_quantity
                      ) 
   select  cart_id, 
           product,
           sold_quantity
   from other_table limit 10 ;
END;

Procedure call

call insert_into_table(5);

Result

select * from my_table;
cart_id product sold_quantity
   5       1    10
   5       2    10
   5       3    10
   5       4    10
   5       5    10
   5       1    10
   5       2    10
   5       3    10
   5       4    10
   5       5    10

https://dbfiddle.uk/PWCgNy0j

Stored procedure to Insert data into table with select statement and argument parameter

1 Answers1