I simply need to migrate +1000 existing addresses from uc_orders to uc_addresses.
I looked at the modules proposing migration, but this was too complicated for me and it relies on PHP.
Here is an SQL query which works "out of the box":
INSERT INTO uc_addresses (uid, first_name, last_name, phone, company, street1, street2, city, "zone", postal_code, country, address_name, created, modified) SELECT DISTINCT ON (uid, last_name, street1, city) * FROM ( SELECT uid , delivery_first_name as first_name, delivery_last_name as last_name, delivery_phone as phone, delivery_company as company, delivery_street1 as street1, delivery_street2 as street2, delivery_city as city, delivery_zone as "zone", delivery_postal_code as postal_code, delivery_country as country, 'Delivery' as address_name, created, modified FROM uc_orders as import WHERE delivery_last_name is not null AND uid > 0 AND uid NOT IN (SELECT uid from uc_addresses) UNION SELECT uid , billing_first_name as first_name, billing_last_name as last_name, billing_phone as phone, billing_company as company, billing_street1 as street1, billing_street2 as street2, billing_city as city, billing_zone as "zone", billing_postal_code as postal_code, billing_country as country, 'Billing' as address_name, created, modified FROM uc_orders as import WHERE delivery_last_name is not null AND uid > 0 AND uid NOT IN (SELECT uid from uc_addresses) ) as result ORDER BY uid
The query will create one or several distinct addresses per user.
* If a user already has addresses in uc_addresses, these addresses remain untouched. This is only for migrating addresses from uc_orders which are NOT in uc_addresses. In some cases, addresses may be old and this is your work to review them after import.
* When there is a single address, the name "Delivery" or "Billing" is unpredictable. But this is always the case in SQL when manipulating DISCTINCT values in order to remove duplicated.
* When there are two different addresses, the first address is the delivery, the second is the billing.
* This SQL was tested under PostgreSQL. MySQL is untested, because we have a ban on MySQL in our company and we only support DB2, Oracle and PostgreSQL.
I would appreciate if you could test this query and try to improve it. Maybe make it an option in the preferences to allow migration of data.