Alter Magento product codes in SQL database?

I have just asked a question on SuperUser regarding Microsoft Excel. Thankfully someone answered - but then I realised that if I alter the SKU column and re-import the product CSV file it won't update the products. It would treat them as new products because the SKU codes don't match.

The question was:

I am trying to edit product codes in Excel so that each product code is exactly 8 digits in le开发者_运维问答ngth.

Currently the product codes are different lengths, for example:

12
1222
213212
32
3231
3213131

The above codes should read as follows:

00000012
00001222
00213212
00000032
00003231
03213131

As you can see all product codes are now 8 digits in length, any product codes that were less than 8 have had the correct number of 0's prepended to them.

It's surprisingly easy in Excel- but is it possible to do it directly on the database tables?

If you're using MS SQL Server:

UPDATE
     Products
SET
    product_code = RIGHT(CONCAT('00000000', product_code), 8)

You just fill in a bunch of 0's at the start and then take the 8 right-most characters.

Create the following PHP file and this should work. I haven't had a chance to test it but I run mysql update scripts with something like this all the time for magento.

<?php

    // initialize Magento environment
    include_once "app/Mage.php";

    Mage::app()->setCurrentStore(0);        
    $connection = Mage::getSingleton('core/resource')->getConnection('core_write');     
    $sql1 = 'update `catalog_product_entity` set sku =  RIGHT(CONCAT('00000000', sku), 8)';     

    $connection->query($sql1);      

?>

For future reference, the SQL command I ran to get this working was:

UPDATE `catalog_product_entity` SET `sku` =  RIGHT(CONCAT('00000000', sku), 8)