Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables

  • tbl_1: id, name, lastname
  • tbl_2: id, name
  • tbl_3: id, lastname

I need to get the values from tbl_1 and then insert in tbl_2 and tbl_3 meaning tbl1.name goes to tbl_2.name and tbl_1.lastname goes to tbl3.lastname. Is this possible using SQL queries?

I'm using MySQL latest

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;
share|improve this answer
    
Hi @rolandomysqldba assuming the ID aren't preserved then I use the same sintax but removing the id field, I'm right? –  ReynierPM Jul 27 '12 at 19:58
    
Updating my answer... –  RolandoMySQLDBA Jul 27 '12 at 20:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.