64

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?

wallyk
  • 56,922
  • 16
  • 83
  • 148
John Nuñez
  • 1,780
  • 13
  • 35
  • 51

6 Answers6

106

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.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
12

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);
Lenka Pitonakova
  • 979
  • 12
  • 14
  • 1
    `Error in query (1242): Subquery returns more than 1 row ` -> syntax is correct, but that's only for single row. – stamster Feb 27 '18 at 15:39
  • Always better to put a limit state when using sub query like the answer. Eg: Select id from modules where title="Top bar" limit 1 – Hashan Jun 26 '20 at 05:57
  • this works, but is there a better way to do it that doesnt involve putting a subquery in each row that i want to add? – shieldgenerator7 Jan 13 '22 at 01:22
4

I was disappointed at the "all or nothing" answers. I needed (again) to INSERT some data and SELECT an id from an existing table.

INSERT INTO table1 (id_table2, name) VALUES ((SELECT id FROM table2 LIMIT 1), 'Example');

The sub-select on an INSERT query should use parenthesis in addition to the comma as deliminators.

For those having trouble with using a SELECT within an INSERT I recommend testing your SELECT independently first and ensuring that the correct number of columns match for both queries.

John
  • 1
  • 13
  • 98
  • 177
2

Your insert statement contains too many columns on the left-hand side or not enough columns on the right hand side. The part before the VALUES has 7 columns listed, but the second part after VALUES only has 3 columns returned: 1, 2, then the sub-query only returns 1 column.

EDIT: Well, it did before someone modified the query....

Andrew Landsverk
  • 673
  • 7
  • 17
0

As a sidenote to the good answer of Michael Berkowski: You can also dynamically add fields (or have them prepared if you're working with php skripts) like so:

   INSERT INTO table_a(col1, col2, col3)
     SELECT
       col1,
       col2,
       CURRENT_TIMESTAMP()
     FROM table_B 
     WHERE b.col1 = a.col1;

If you need to transfer without adding new data, you can use NULL as a placeholder.

angularNoob
  • 27
  • 7
0

If you have multiple string values you want to add, you can put them into a temporary table and then cross join it with the value you want.

-- Create temp table    
CREATE TEMPORARY TABLE NewStrings (
        NewString VARCHAR(50)
    );
-- Populate temp table
INSERT INTO NewStrings (NewString) VALUES ('Hello'), ('World'), ('Hi');

-- Insert desired rows into permanent table
INSERT INTO PermanentTable (OtherID, NewString)
    WITH OtherSelect AS (
        SELECT OtherID AS OtherID FROM OtherTable WHERE OtherName = 'Other Name'
    )
    SELECT os.OtherID, ns.NewString
    FROM OtherSelect os, NewStrings ns;

This way, you only have to define the strings in one place, and you only have to do the query in one place. If you used subqueries like I initially did and like Elendurwen and John suggest, you have to type the subquery into every row. But using temporary tables and a CTE in this way, you can write the query only once.

shieldgenerator7
  • 1,507
  • 17
  • 22