Magento Articles and Tips for Developers

 
/* STEP 1 :: UPDATE ONLY ADDRESSES WHICH HAVE TURKEY AS A COUNTRY IN MAGENTO DB */
UPDATE mytable my
INNER JOIN customer_address_entity c1 ON my.magento_id = c1.parent_id
INNER JOIN customer_address_entity_varchar c3 ON c1.entity_id = c3.entity_id AND c3.entity_type_id = 2 AND c3.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='country_id' AND entity_type_id=2) 
INNER JOIN customer_address_entity_int c2 ON c1.entity_id = c2.entity_id AND c2.entity_type_id = 2 AND c2.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='region_id' AND entity_type_id=2)
INNER JOIN directory_country_region b ON m.shipping_region = b.code AND b.country_id = 'US'
SET my.shipping_region_id = b.region_id
WHERE c3.VALUE='US' AND my.shipping_country_id='US' AND c2.VALUE=0;
 
/* CHECK NUMBER OF RECORDS FOR STEP 2 */
SELECT * FROM customer_address_entity_int c2
INNER JOIN customer_address_entity c1 ON c2.entity_id = c1.entity_id AND c2.entity_type_id = 2 AND c2.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='region_id' AND entity_type_id=2)
INNER JOIN customer_address_entity_varchar c3 ON c1.entity_id = c3.entity_id AND c3.entity_type_id = 2 AND c3.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='country_id' AND entity_type_id=2) 
INNER JOIN mytable my ON my.magento_id = c1.parent_id
WHERE my.shipping_region_id > 0 AND c3.VALUE='US' AND m.shipping_country_id = 'US';
 
/* STEP 2 */
UPDATE magento01.customer_address_entity_int c2
INNER JOIN customer_address_entity c1 ON c2.entity_id = c1.entity_id AND c2.entity_type_id = 2 AND c2.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='region_id' AND entity_type_id=2)
INNER JOIN customer_address_entity_varchar c3 ON c1.entity_id = c3.entity_id AND c3.entity_type_id = 2 AND c3.attribute_id = 
(SELECT attribute_id FROM eav_attribute WHERE attribute_code='country_id' AND entity_type_id=2) 
INNER JOIN mytable my ON my.magento_id = c1.parent_id
SET c2.VALUE = my.shipping_region_id 
WHERE c2.VALUE=0 AND my.shipping_region_id > 0 AND c3.VALUE='US' AND m.shipping_country_id = 'US';