Insert mysql query

I have a product table and a product varieties table.

**Product Table**
pid
articlenum
desc
price

**Product Varities Table**
pvid,
sizeid,
materialid,
pid fk
quantity
barcode

I am creating an Interface where a user can ADD a new product, I have a stored proc, with two statements. First will execute insert into product table and the other into product varieties table.

Issue I have is, how do I insert a product id into product varieties table using the article id – each article can have multiple varieties such as s, m, l, xl, xs?

Using the code below, I cannot do that because it returns more than one record on select for each article.

INSERT INTO product 
  (articlenum,pdesc,price) 
VALUES 
  (articlenum, pdesc,price);

set @productid = select DISTINCT product.product_id 
  from product 
  where product.articlenum = articlenum;

INSERT INTO product_varities
  (sizeid,materialid,pid,quantity,barcodevalue) 
VALUES 
  (sizeid, materialid, @productid, quantity, barcode);

Tried:

set @productid = select product.pid 
  from product 
  where product.articlenum = articlenum;

This returns result like:

PID
1
2
3

Required Result:

PID
1

If I am unclear, please ask.


Source: syntax

Leave a Reply