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')
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.
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' )
