INSERT INTO with SubQuery MySQL

Question

I have this Statement:

INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)     VALUES (1, 2, (SELECT item_costprice FROM qa_items WHERE item_code = 1)); 

I'm trying to insert a value copy the same data of item_costprice, but show me the error:

Error Code: 1136. Column count doesn't match value count at row 1 

How i can solve this?

Answer 1

Use numeric literals with aliases inside a SELECT statement. No () are necessary around the SELECT component.

INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)   SELECT     /* Literal number values with column aliases */     1 AS item_code,     2 AS invoice_code,     item_costprice   FROM qa_items    WHERE item_code = 1; 

Note that in context of an INSERT INTO...SELECT, the aliases are not actually necessary and you can just SELECT 1, 2, item_costprice, but in a normal SELECT you'll need the aliases to access the columns returned.

Answer 2

You can just simply e.g.

INSERT INTO modulesToSections (fk_moduleId, fk_sectionId, `order`) VALUES     ((SELECT id FROM modules WHERE title="Top bar"),0,-100);