CODEWITHSUNDEEP
mysqlsql
Solace
Solace

Reputation: 9010

How do we insert "data from more than one records", from one table to another?

Following command is used to insert data from one table into another.

insert into school_record (phone_id)
    select phone_id from students where name="abc"
    order by phone_id asc;

But what if I want to insert all the phone_id values for the names "abc", "def", "ghi" and so on...

That is how to insert data from one table to another table, after selecting values from more than one records?

Upvotes: 0

Views: 113

Answers (4)

Ronak Shah
Ronak Shah

Reputation: 1549

If you want to insert data with constant name then you can try below:

insert into school_record (phone_id)
    select phone_id from students where name in ("abc","def","xyz")
    order by phone_id asc;

if you want to insert phone_id for the name that contain by some another table then try below:

insert into school_record (phone_id)
    select phone_id from students where name in (select name from tablename)
    order by phone_id asc;

refer "in" from below: https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

Upvotes: 2

djm.im
djm.im

Reputation: 3323

Use IN() - in brackets list all names which you need to insert

insert into school_record (phone_id)
select phone_id 
from students 
where name in ('abc', 'def', 'ghi', ...)
;

Or if you need to insert all 

insert into school_record (phone_id)
select phone_id 
from students 
;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Only your WHERE clause limits the number of records. Make it 

where Name = 'abc' or name = 'def' or name = 'ghi'

or

where name in ('abc','def','ghi')

Upvotes: 2

stackrocha
stackrocha

Reputation: 405

If you want all the phone_ids of the students table you can simply do:

insert into school_record (phone_id) select phone_id from students  order by phone_id asc;

Upvotes: 2

Related Questions