mySQL script to update non-unique URL titles

Question

We need to update a set of url-titles that have some duplicate entries (migrated from a non-EE site). Because of the size of the database I would prefer to use direct mysql method, but I am open to any solutions out there.

If we have records:

a-stoney
a-stoney
ab-dille
ab-dille
ab-dille
ac-gifta
ae-jones
ae-jones

I want to be able to run against the exp_channel_titles table and end up with ONLY the duplicate entries updated as follows:

a-stoney
a-stoney-1
ab-dille
ab-dille-1
ab-dille-2
ac-gifta
ae-jones
ae-jones-1

Or some similar output, where non-duplicate url-titles are not edited, and any duplicates are applied a convention similar to the native EE behavior when attempting to create a new entry (with a title that already exists in the system)

Here is the SELECT I am using to display the duplicate entries, just having trouble applying the count and converting to an UPDATE Thanks!

Answer 1

How big is the database? Something like the below (untested) should do it with PHP

$sql = "SELECT * FROM exp_channel_titles";
$results = //execute query

$urls = array();
foreach($results as $result)
{
    $count = 1;
    $url = $result['url_title'];
    $entry_id = $result['entry_id'];

    while(isInArray($url, $urls))
    {
        $url = $result['url_title'] . '-' . $count;
        $count++;
    }
    $urls[] = $url;
    $sql = "UPDATE exp_channel_titles SET url_title = '$url' WHERE entry_id = $entry_id";
    //execute query;
}

function isInArray($string, $array)
{
    return in_array($string, $array);
}