Solved

How can I update two new tables from one old table.

Posted on 2013-12-29
4
263 Views
Last Modified: 2014-01-22
Hello, I'm doing a data conversion from a old cart to a new one. I'm able to update the old customer info into the new table however with my new cart I have two tables.

Table 1: customers_new that has address, city, zip and general info.
Table 2: customers_stateprov_new that has the state id linked to it (data and structure below)

record_id | customer_state_customer_id |  customer_state_stateprov_id  | customer_state_destination

1 | 1000  | 37 | BillTo
2 | 1000  | 37 | ShipTo
3 | 1001  | 1   | BillTo
4 | 1001  | 1   | ShipTo

The old customer table has all of the info tied into the one table so I'm wanting to build out the customers_stateprov table with the old table data. I was able to export the old table to the new table following this basic query but as you can see it doesn't solve last step of building out the stateprov table

INSERT INTO tbl_new_customers (cust_id, cust_name, cust_addr1)
SELECT customer_id, customer_name, customer_address1
FROM tbl_old_customers
0
Comment
Question by:brihol44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39745085
a few questions that will help in solving your problem

how is the state saved in the old table.
does it have the id saved with it
was the state saved from a menu choice or did the user type in the state.
0
 

Author Comment

by:brihol44
ID: 39745211
    how is the state saved in the old table.

locState is the column name in the OLD TABLE and it has the 2 letter abbreviation so CA (for California). I was hoping I could just link it to my state table once I figured out the first step.  

My NEW STATE TABLE looks like...

state_prov_id | state_prov_code | state_prov_name

1  | CA | California
2  | OR | Oregon
3  | WA |Washington




    does it have the id saved with it


No, it just had a two state abbreviation like above.


   was the state saved from a menu choice or did the user type in the state.

it was a menu choice.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39745449
you will need to map either the name or the  2 letter abbreviation to you state ids,

the  state id's should relate to the state name somewhere in your new tables, as you used menu choice for your states in your old table you wont have spelling mistakes in the state name so you should be able match on the name.

if you are starting from scratch and the ids dont yet relate to a state then your first step will be to assign ids to each state then you will be able to import the data
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 39750761
I assume customers_stateprov_new.record_id is auto_increment and state_destination is the column from the tbl_old_customers that will be used for customer_state_destination.  If I understand it correctly this should do it:

INSERT INTO customers_stateprov_new (customer_state_customer_id, customer_state_stateprov_id, customer_state_destination)
SELECT o.customer_id, s.state_prov_id, o.state_destination
FROM tbl_old_customers o 
JOIN state s ON (o.locState=s.state_prov_code);

Open in new window

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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