What wrong with this query?

Question

INSERT INTO 
product_search_table (id, score) 
VALUES (
SELECT product_id, 50 FROM cart_product WHERE product_id = 555
)

Is giving me an error. What am I doing wrong?

555 is going to be replaced with a variable.

Answer 1

Try

INSERT INTO 
product_search_table (id, score) 
SELECT product_id, 50 FROM cart_product WHERE product_id = 555

Answer 2

Brackets around the select look suspect - as does the VALUES. Try:

INSERT INTO product_search_table (id, score) 
SELECT product_id, 50 FROM cart_product WHERE product_id = 555

Answer 3

Use:

INSERT INTO product_search_table 
(id, score) 
SELECT product_id, 50 
  FROM cart_product 
 WHERE product_id = 555

When using a SELECT statement in an INSERT statement, you don't use the VALUES keyword or enclose the SELECT in brackets.

Answer 4

You're using incorrect syntax for an INSERT...SELECT query.

Here's how you'd do it properly:

INSERT INTO product_search_table (id, score) 
SELECT product_id, 50 FROM cart_product WHERE product_id = 555

More information: MySQL 5.1 Reference: INSERT..SELECT Syntax

Answer 5

Seems to be a syntax issue. VALUES doesn't belong in this type of query.

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

Answer 6

My guess is that the subquery could return multiple rows (in theory). So how could multiple rows fit into a single field? You could try to make sure the queried data is a scalar value.

(I'm also not too sure about the second field/the 50 value).

edit: VALUES is used in insert statements people. See this page

edit2: You're only selecting data for the first column, where is the data for the second column?

Answer 7

you can't do that with the VALUES.

for example:

INSERT INTO 
 product_search_table (id, score) 
SELECT product_id,50 FROM cart_product WHERE product_id = 555