I'm having a bit of a problem with customer accounts being linked to the wrong orders. This is the chain of events:
- I cloned a Magento site into a subdomain
- I updated Magento and changed template on the subdomain, which took a few weeks
- Meanwhile several new orders and customers were created in the live site
- Just before putting the new site live, I exported customers using Dataflow Profile - all seemed to work well
- I copied sales tables from old database to new one, making sure to update eav_entity_store entries as explained on some forums
- I launched the new site
It all seemed to go well, but now the store owners have pointed out that some customer accounts in the admin are linked to the wrong orders.
After investigating, I worked out what has happened. The old store had a mysterious duplicate entry for one of the customers: the same customer had two identical accounts but with two consecutive IDs. When I imported customers into the new store using Dataflow, the duplicate entry was removed, but all subsequent IDs were reduced by 1. So a customer that had ID 10056 in the old store, now has ID 10055 in the new one. So when you click on that customer's information, the Recent Orders tab shows the orders for the customer who had ID 10055 in the old store.
I did consider manually changing the customer IDs one by one in phpMyadmin, but I'm worried about messing up the database completely and breaking the live site. The other problem is that the new site has now been live for a few days and there are new customers with new orders, which are syncing with each other correctly. So if I try to increase the out of sync IDs by 1, I'll be forced to also change the IDs of the new customers whose orders are showing correctly, potentially messing up THEIR orders. I only have limited understanding of how the customer tables in the database are linked to orders, so I can see no safe and immediate solution.
I hope I explained the problem clearly, I realise it's a bit complicated and it took me a while to get my head around it.
Can anyone suggest a safe way to re-attach orders to the correct customers?
You are in a catch 22 right now. Dam if you do, dam if you don't.
I believe the correct solution is by doing the manually. Are we talking about a few hundred orders or thousands of orders?
As a first step, I would take this down to your local environment and play around with it. See if you can correctly manually do it.
I am trying to come up with a SQL query for you to use to match one column to another and update it right now. DO you have a CSV with the old customer ID with the new one?