Moving table columns to another table question

[mididelight]

Hello,

This might be an easy one but I cant seem to wrap my head around it. I have two db tables:

- customers
- orders

and a third table that associates orders to customers called:

customer_order_assc

I want to move columns col1,col2,col3 from the orders table to the customers table. But I have to make sure the appropriate data from the columns corresponds to the right customer for that order (which I think would be used from the customer order association table).

The customer table index is: customer_id
The order table index is : order_id
The customer_order_assc table columns are:
customer_id
order_id

So that I can track which orders 1 customer has.

Does anyone know how to accomplish this?

Thanks in advance.

[mattv10]

Hey!

Why do you physically have to move the data? Shouldn't the relationships do this for you? Think about this. Customers and Orders is a many to many relationship so your correct about having an assoc table.

That is all you will need though. If you put col1 2 and 3 into the assoc table you will have redundant data..


You can then query and keep track of which order 1 customer has..

Hope this helps! :)

[zackbloom]

There is a flaw in your database design. You should have 2 tables customer and order. Since their is a one to many relanship between the customers and the orders you have the customer id in the order table. Here's an example:

Customer:

id
name
address

Order:

id
customerid
items
time

[mididelight]

mattv10, your mistaken , i do not want to move the columns from orders to customer_orders_assc, but from orders to customers.

zackbloom: you have a good point, but that doesnt really answer my question. The 3 columns I want moved correspond to the customers data and not the order, thats why it needs to be moved.

do you know how to accomplish this query?

[aussie girl]

have a look at the ALTER table statements

[NightShift58]

Zackbloom is right: You should only use 2 tables.

You're also right when you say that customer data belongs in the customer table.

Step 1 would be to ALTER your customer table and add the 3 new fields that are to receive the field value from the orders table. You can do this with a script or with phpMyAdmin - it doesn't matter as it's a on-time thing.

Then, run the following INSERT query:INSERT INTO `customers` ( customer_id, customer_col1, customer_col2, customer_col3 )
SELECT a.coassoc_customer_id, b.order_customer_col1, b.order_customer_col2, b.order_customer_col3
FROM customer_order_assc a, orders b
WHERE a.coassoc_order_id = b.order_id
ON DUPLICATE KEY UPDATE
customer_col1 = VALUES(customer_col1),
customer_col2 = VALUES(customer_col2),
customer_col3 = VALUES(customer_col3)Finally, alter your orders table to delete the 3 columns you don't bed anymore.

Since you're going to have to rewrite code anyway, might as well get rid o the assoc as well.

[mididelight]

thank you guys for your help. i actually was able to move the data from the orders table to the customers table. It took me a while but i figured it out.

you guys are definitely right about not having the customer_order_assc table, i already started the process of updating my code to reflect the db schema changes.

thanks again.