brihol44
asked on
How can I update two new tables from one old table.
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
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
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
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
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.
ASKER
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.