MySQL INSERT with multiple nested SELECTs

Question

Is a query like this possible? MySQL gives me an Syntax error. Multiple insert-values with nested selects...

INSERT INTO pv_indices_fields (index_id, veld_id)
VALUES
('1', SELECT id FROM pv_fields WHERE col1='76' AND col2='val1'),
('1', SELECT id FROM pv_fields WHERE col1='76' AND col2='val2')

Answer 1

I've just tested the following (which works):

insert into test (id1, id2) values (1, (select max(id) from test2)), (2, (select max(id) from test2));

I imagine the problem is that you haven't got ()s around your selects as this query would not work without it.

Answer 2

When you have a subquery like that, it has to return one column and one row only. If your subqueries do return one row only, then you need parenthesis around them, as @Thor84no noticed.

If they return (or could return) more than row, try this instead:

INSERT INTO pv_indices_fields (index_id, veld_id)   
   SELECT '1', id 
   FROM pv_fields 
   WHERE col1='76' 
   AND col2 IN ('val1', 'val2')

or if your conditions are very different:

INSERT INTO pv_indices_fields (index_id, veld_id)
    ( SELECT '1', id FROM pv_fields WHERE col1='76' AND col2='val1' )
  UNION ALL
    ( SELECT '1', id FROM pv_fields WHERE col1='76' AND col2='val2' )