Solved

How can I update/add existing data from one set of tables to another?

Posted on 2013-05-26
4
225 Views
Last Modified: 2013-05-27
Hello,

I have a old set of tables that I want to import into a new set of tables from. I've copied the old tables into the new db to make thing easier but the table structure is a little different for the new tables.

How can I copy the data from the old tables into the new ones?

So for example...

OLD TABLE REF
tbl_old_customers
tbl_old_orders


NEW TABLE REF
tbl_new_customers
tbl_new_orders


OLD TABLE CUSTOMERS: customer_id, customer_name, customer_address1, customer_city => NEW TABLE CUSTOMERS: cust_id, cust_name, cust_addr1

OLD TABLE ORDERS: customer_id, order_date, order_payment_type => NEW TABLE ORDERS: cust_id, ord_date, pay_type

Thanks for the help, I believe once somebody shows me how to do the customers table, I can figure out the orders table unless there's some sort of inner join process that can happen to do both.

Brian
0
Comment
Question by:brihol44
4 Comments
 
LVL 16

Assisted Solution

by:santoshmotwani
santoshmotwani earned 167 total points
ID: 39198509
INSERT INTO new_table_customers
            (cust_id,
             cust_name,
             cust_addr1)
SELECT *
FROM   (SELECT customer_id       AS cust_id,
               customer_name     AS cust_name,
               customer_address1 AS cust_addr1
        FROM   old_table_customers);
0
 
LVL 6

Assisted Solution

by:Peter Kiprop
Peter Kiprop earned 166 total points
ID: 39198540
Try the below

insert into tbl_new_customers
select  customer_id, customer_name, customer_address1 from tbl_old_customers

insert into tbl_new_orders
select  customer_id, order_date, order_payment_type from tbl_old_orders
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 167 total points
ID: 39198548
I just had to do this earlier this evening.  This is what I used from http://stackoverflow.com/questions/7482443/how-to-copy-data-from-one-table-to-another-new-table-mysql  Note that the order and number of fields must match just like they would on a normal INSERT.
INSERT INTO tbl_new_customers (cust_id, cust_name, cust_addr1)
SELECT customer_id, customer_name, customer_address1
FROM tbl_old_customers

Open in new window

0
 

Author Closing Comment

by:brihol44
ID: 39199310
Thanks everyone!

Brian
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
performance query 4 32
SQL Syntax 24 46
Access join syntax when converting to T-SQL query 4 37
Need return values from a stored procedure 8 23
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question