From 9780fbc0c56b386efe9887aa22864ed9eba55363 Mon Sep 17 00:00:00 2001
From: sarehag <joakim.sarehag@gmail.com>
Date: Thu, 28 Jan 2016 15:07:23 +0100
Subject: [PATCH] CRM-17852 - Misspeled state names in Estonia/Lithuania -
 updated where statements

---
 CRM/Upgrade/Incremental/sql/4.7.1.mysql.tpl | 46 ++++++++++++---------
 1 file changed, 26 insertions(+), 20 deletions(-)

diff --git a/CRM/Upgrade/Incremental/sql/4.7.1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.7.1.mysql.tpl
index c322e02296..3dc0b34d14 100644
--- a/CRM/Upgrade/Incremental/sql/4.7.1.mysql.tpl
+++ b/CRM/Upgrade/Incremental/sql/4.7.1.mysql.tpl
@@ -2,25 +2,31 @@
 # CRM-17852 - Misspeled state names in Estonia/Lithuania
 
 #Estonia
-UPDATE civicrm_state_province SET name = 'Harjumaa' where id = 2376;
-UPDATE civicrm_state_province SET name = 'Hiiumaa' where id = 2377;
-UPDATE civicrm_state_province SET name = 'Ida-Virumaa' where id = 2378;
-UPDATE civicrm_state_province SET name = 'Jõgevamaa' where id = 2379;
-UPDATE civicrm_state_province SET name = 'Järvamaa' where id = 2380;
-UPDATE civicrm_state_province SET name = 'Läänemaa' where id = 2381;
-UPDATE civicrm_state_province SET name = 'Lääne-Virumaa' where id = 2382;
-UPDATE civicrm_state_province SET name = 'Põlvamaa' where id = 2383;
-UPDATE civicrm_state_province SET name = 'Pärnumaa' where id = 2384;
-UPDATE civicrm_state_province SET name = 'Raplamaa' where id = 2385;
-UPDATE civicrm_state_province SET name = 'Saaremaa' where id = 2386;
-UPDATE civicrm_state_province SET name = 'Tartumaa' where id = 2387;
-UPDATE civicrm_state_province SET name = 'Viljandimaa' where id = 2389;
-UPDATE civicrm_state_province SET name = 'Võrumaa' where id = 2390;
+
+SET @EstoniaCountryId = (SELECT id FROM civicrm_country cc WHERE cc.name = 'Estonia');
+
+UPDATE civicrm_state_province SET name = 'Harjumaa' where name='Harjumsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Hiiumaa' where name='Hitumea' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Ida-Virumaa' where name='Ida-Virumsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Jõgevamaa' where name='Jogevamsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Järvamaa' where name='Jarvamsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Läänemaa' where name='Lasnemsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Lääne-Virumaa' where name='Laane-Virumaa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Põlvamaa' where name='Polvamea' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Pärnumaa' where name='Parnumsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Raplamaa' where name='Raplamsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Saaremaa' where name='Saaremsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Tartumaa' where name='Tartumsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Viljandimaa' where name='Viljandimsa' AND country_id = @EstoniaCountryId;
+UPDATE civicrm_state_province SET name = 'Võrumaa' where name='Vorumaa' AND country_id = @EstoniaCountryId;
 
 #Lithuania
-UPDATE civicrm_state_province SET name = 'Klaipėdos Apskritis' where id = 3514;
-UPDATE civicrm_state_province SET name = 'Marijampolės Apskritis' where id = 3515;
-UPDATE civicrm_state_province SET name = 'Panevėžio Apskritis' where id = 3516;
-UPDATE civicrm_state_province SET name = 'Šiaulių Apskritis' where id = 3517;
-UPDATE civicrm_state_province SET name = 'Tauragės Apskritis' where id = 3518;
-UPDATE civicrm_state_province SET name = 'Telšių Apskritis' where id = 3519;
+
+SET @LithuaniaCountryId = (SELECT id FROM civicrm_country cc WHERE cc.name = 'Lithuania');
+
+UPDATE civicrm_state_province SET name = 'Klaipėdos Apskritis' where name='Klaipedos Apskritis' AND country_id = @LithuaniaCountryId;
+UPDATE civicrm_state_province SET name = 'Marijampolės Apskritis' where name='Marijampoles Apskritis' AND country_id = @LithuaniaCountryId;
+UPDATE civicrm_state_province SET name = 'Panevėžio Apskritis' where name='Panevezio Apskritis' AND country_id = @LithuaniaCountryId;
+UPDATE civicrm_state_province SET name = 'Šiaulių Apskritis' where name='Sisuliu Apskritis' AND country_id = @LithuaniaCountryId;
+UPDATE civicrm_state_province SET name = 'Tauragės Apskritis' where name='Taurages Apskritis' AND country_id = @LithuaniaCountryId;
+UPDATE civicrm_state_province SET name = 'Telšių Apskritis' where name='Telsiu Apskritis' AND country_id = @LithuaniaCountryId;
-- 
2.25.1