0

i am in the middle of migrating a website from cubecart v3 to opencart 2.0 using phpmyadmin and have just got the last important bit to do,

but the ID for countries and counties are different

this is what i had in mind

UPDATE `oc_address` SET `country_id`=
(SELECT country_id
FROM `oc_country`
WHERE `oc_country`.`name`=`shop_old`.`CubeCart_iso_countries`.`printable_name`
AND `shop_old`.`CubeCart_iso_countries`.`id`=`shop_old`.`CubeCart_customer`.`country`
AND `oc_address`.`customer_id`=`shop_old`.`CubeCart_customer`.`customer_id`)

but i keep getting unkown coloumn error i even tried copying the iso countries table from old database to the new one and point at that for reference but everytime i get unkown coloumn in where clause, i even got it for oc.address.customer_id

i am very new to SQL (today is first time i have used ever) i have migrated everything else fine, but i need to migrate the zone_ID's for customers address for counties and countries, i would prefer to keep the new database as is, and thought i could reference the new tables country ID when the country name matched the name on the old table and the zone-ID on the old table matched the zone-ID on the customers old address table

just for example,
old DB = UK ID is 225, Angus ID is 185,
New DB = UK ID is 222, Angus ID is 3516

how can i update or insert data to correct the country and zone ID's ?

thanks in advance to anyone that spares their time to reply happy Santa's day to all

Yoboots Tech
  • 31
  • 1
  • 3

1 Answers1

0

in the end i copied the old cubecart_iso_countries table to the new Opencart database inserted all customers address'from old DB

then run the following UPDATE oc_address SET country_id = ( SELECT country_id FROM oc_country INNER JOIN CubeCart_iso_countries ON oc_country.name = CubeCart_iso_countries.name WHERE CubeCart_iso_countries.id = oc_address.country_id )

and it then updated the country ID in all customer address correctly

Yoboots Tech
  • 31
  • 1
  • 3