How can I update values in database table using an upgrade script?

Question

I am trying to UPDATE values inside of the directory_country table using a data-upgrade script but hit a brick wall with the syntax for updating a table using this method as I am trying to avoid using direct SQL.

Here's what I have so far but it doesn't work at the moment:

<?php 
$installer = $this;
$installer->startSetup();

$installer->run("
UPDATE `{$installer->getTable('directory_country')}` SET (`country_id`, `iso2_code`, `iso3_code`, `restriction_set`) WHERE
('GG', 'GG', 'GGY', '0'),('AD', 'AD', 'ADY', '7'),('AI', 'AI', 'AIA', '7'),('AG', 'AG', 'ATG', '7');
");

$installer->endSetup();

Could anyone suggest the best way to do this please?

Answer 1

Try working with objects and save methods. Define an array with the data you need updating

$config = array(
    'GG' => array(
        'iso2_code' => 'GG', 
        'iso3_code' => 'GGY', 
        'restriction_set' => 0
    ),
    'AD' => array(
        'iso2_code' => 'AD', 
        'iso3_code' => 'ADY', 
        'restriction_set' => 7
    ),
    //add the rest of the data here following the same pattern
);

Then loop through the array, load the country, set the data and save

foreach ($config as $key => $data) {
     $country = Mage::getModel('directory/country')->load($key);
     $country->addData($data);
     $country->save();
}

I know that using load and save in loops is not the best way to do it, but since this is an upgrade script that should run only once you should be OK with it.