Sunday, April 17, 2005

MySQL INSERT SELECT example

insert into user (id, name, email, passwd, ref, joindate) select idm, user_name, email, pass, refferal, sysdate() from db.members;

18 comments:

hungrycoder said...

thanks, i was looking for this syntax.

Fordy said...

When I've not done any MySql for a while I forget the syntax. Thanks for this.

BTW - Cool idea for a blog \ code library!

Fordy said...

When I've not done any MySql for a while I forget the syntax. Thanks for this.

BTW - Cool idea for a blog \ code library!

Anonymous said...

Thanks for the script..

aktenium said...

thanks. quick but descriptive.

Anonymous said...

Is it possible to do this from 2 tables? Insert id from table1 select from table2

s10 said...

thanx
easy & useful

serban said...

10x, simple and effective

whiteTgr said...

thnx a lot. Whitetgr from Mexico City

Anonymous said...

yeahhh thankkss

Anonymous said...

very helpfully to me

Anonymous said...

insert into blgusers-trig (id,firstname,lastname) select id,firstname,lastname from blgusers where id=2;

i am trying this.. but i am getting an error as "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 '-trig (id,firstname,lastname) select id,firstname,lastname from blgusers where i' at line 1"

both the tables have same structures. except blgusers-trig does not have id as primary key, not null and auto increment.

let me know where is the problem in the code.

i am using MySQL 1.2.12

Jorge said...

When using LIMIT I noticed a drop of 5 in the auto-increment field.

I want to insert the first 10 elements of several tables into tablenew plus another field with the a text of my choice.

INSERT into tablenew (c1, c2, c3) SELECT a1, a2, 'list1' from table1 LIMIT 0,10

tablenew autoincrement last record: 10

INSERT into tablenew (c1, c2, c3) SELECT b1, b2, 'list2' from table2 LIMIT 0,10

tablenew autoincrement next record: 16 instead of 11 !!! why?

Manivannan N said...

You can use like this

insert into blgusers-trig (id,firstname,lastname) (select id,firstname,lastname from blgusers where id=2);

Abdeali said...

Thnx for this Syntax

Right Processor said...

insert into tbl_emaillist(emailId)

SELECT emailId FROM tbl_emaillist WHERE emailId <> 'xyz@hotmail.com'

VALUES ('xyz@hotmail.com');

Anonymous said...

how to insert data in two tables using mysql..

computergenius said...

insert into blgusers-trig (id,firstname,lastname)
won't work, because you can't use '-' in a table name.

Change your table to blgusers_trig