mysql multiple Insert where select many column from another table

Question

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

ADyson · Accepted Answer · 2018-04-17 13:42:42Z

1

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.

answered Apr 17, 2018 at 13:42

ADyson

62.9k16 gold badges86 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

isaace · Accepted Answer · 2018-04-17 13:43:59Z

1

You can just do it like this:

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

answered Apr 17, 2018 at 13:43

isaace

3,4291 gold badge11 silver badges26 bronze badges

1 Comment

MTK Over a year ago

Thank's ... You have upvote but I will accept ADyson answer because him have first

Collectives™ on Stack Overflow

mysql multiple Insert where select many column from another table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related