Solved

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

Posted on 2013-05-26
4
222 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now