CODEWITHSUNDEEP
mysqlsql
MTK
MTK

Reputation: 3580

mysql multiple Insert where select many column from another table

I have the following data:

table1:

id  common_criteria        member_id
1       some                 1000
2       some1                 500
3       some                  100
4       some                  200
3       some1                2000

table2

id member_id

The id columns are AUTOINCREMENT PRIMARY KEY on both tables

I need to insert into table2 three new rows (in this case) where common_criteria from table1 = 'some'

Expected result for table2:

id    member_id
1       1000
2       100
3       200

I have tried:

INSERT into table2 (`member_id`) 
VALUES (
 SELECT `member_id` FROM table1 WHERE common_criteria = 'some'
)

But I get a syntax error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT member_id FROM table1 WHERE common_criteria = 'some')' at line 1

Upvotes: 1

Views: 61

Answers (2)

isaace
isaace

Reputation: 3429

You can just do it like this: 

INSERT INTO table2 (`member_id`) 
SELECT `member_id` FROM table1 WHERE common_criteria = 'some'

Upvotes: 1

ADyson
ADyson

Reputation: 62098

You shouldn't need the VALUES part when you want to get the results from a SELECT. Instead you can simplify it to this:

INSERT into table2 (`member_id`) 
   SELECT `member_id`
   FROM table1 
   WHERE common_criteria = 'some'

and it will insert all the rows returned by the SELECT.

Additional note (since people often ask this): If you had a scenario where some of the values are static every time, then you can do that too quite easily:

INSERT into table2 (`member_id`, `some_other_field`) 
   SELECT `member_id`, 'Static Text' 
   FROM table1 
   WHERE common_criteria = 'some'

In this case "Static Text" will be inserted every time into every row, no matter how many rows are returned (and inserted) as a result of the SELECT.

Upvotes: 1

Related Questions