Using insert into ... select results in a incorrect syntax near select, why?

How can I make a SELECT inside an INSERT operation?

i开发者_Python百科nsert into tableX (a_id, b_id) 
VALUES ((SELECT service_id 
         FROM tableY 
         WHERE id = 10, 2));

But the server returns a syntax error, why?

SQL Error [156] [S0001]: Incorrect syntax near the keyword 'select'.

While my original answer gave a working solution, I was actually wrong about the cause of the error. There is nothing wrong with using a scalar subquery inside a VALUES clause. The problem with the statement in the question is simply that one parenthesis is in the wrong place; the scalar subquery must be enclosed in parentheses.

This should work:

insert into tableX (a_id, b_id) 
VALUES (
  (SELECT service_id 
         FROM tableY 
         WHERE id = 10)
  , 2
  );

Original Answer

VALUES can only be used in conjunction with literal values. However, literal values can be used in a subquery. Do this:

insert into tableX (a_id, b_id) SELECT service_id, 2 FROM tableY WHERE id = 10

You don't need the values key word and also you can add the default value 2 for b_id column in the select list instead of adding it after the SELECT statement

Try this:

INSERT INTO tableX (a_id, b_id) 
SELECT service_id, 2 
  FROM tableY 
 WHERE id = 10

you don't need the values word.

here is some documentation for mysql

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

also, when you specify the columns into which the values should be inserted, you need to make sure your select returns the same number/types appropriate to what you specified.

You're telling the DBMS that you're inserting two values (a_id, b_id) but only selecting one (service_id).

Try:

insert into tableX (a_id, b_id)
SELECT service_id, 4 as QUESTIONMARK FROM tableY WHERE id in (10, 2);

This works on many database engines however we don't know what environment you are working on.

EDIT: What is the second insert supposed to be?

In MySQL one can insert dynamic values. Here is the sample:

INSERT INTO Item_Info (`Back_Ground_Color`,) 
VALUES ( (select concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6))));