Sql INSERT INTO combining value and select failure

Question

I wanted to insert two values, one is filled by a fixed number and the other one is the id from another table.

Now I got the error

#1242 - Subquery returns more than 1 row.

INSERT INTO table1 (value1, value2) VALUES 
(6 , (SELECT id FROM table2 WHERE name = 'Peter'))

Maybe you can help me.

Answer 1

If you want to insert record into your table1 for every record in table2 where name is Peter this approach should work.

This insert query will insert all records from table2 where name is "Peter" into table1. If you want to insert only one record you could use LIMIT as Macmee has explained in his answer

insert into dbo.table1
(
     value1,
     value2
)(
     select 
          6,
          table2.id 
     from 
          table2
     where
          name = 'Peter'
)