We need to update a set of titles that have some duplicate entries. 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 (url_title):
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 table and end up with ONLY the duplicate url_titles 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 similar convention
Table syntax:
CREATE TABLE `exp_channel_titles` (
`entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`site_id` int(4) unsigned NOT NULL DEFAULT '1',
`channel_id` int(4) unsigned NOT NULL,
`author_id` int(10) unsigned NOT NULL DEFAULT '0',
`forum_topic_id` int(10) unsigned DEFAULT NULL,
`ip_address` varchar(45) NOT NULL DEFAULT '0',
`title` varchar(100) DEFAULT NULL,
`url_title` varchar(75) NOT NULL,
`status` varchar(50) NOT NULL,
`versioning_enabled` char(1) NOT NULL DEFAULT 'n',
`view_count_one` int(10) unsigned NOT NULL DEFAULT '0',
`view_count_two` int(10) unsigned NOT NULL DEFAULT '0',
`view_count_three` int(10) unsigned NOT NULL DEFAULT '0',
`view_count_four` int(10) unsigned NOT NULL DEFAULT '0',
`allow_comments` varchar(1) NOT NULL DEFAULT 'y',
`sticky` varchar(1) NOT NULL DEFAULT 'n',
`entry_date` int(10) NOT NULL,
`year` char(4) NOT NULL,
`month` char(2) NOT NULL,
`day` char(3) NOT NULL,
`expiration_date` int(10) NOT NULL DEFAULT '0',
`comment_expiration_date` int(10) NOT NULL DEFAULT '0',
`edit_date` bigint(14) DEFAULT NULL,
`recent_comment_date` int(10) DEFAULT NULL,
`comment_total` int(4) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`entry_id`),
KEY `channel_id` (`channel_id`),
KEY `author_id` (`author_id`),
KEY `url_title` (`url_title`),
KEY `status` (`status`),
KEY `entry_date` (`entry_date`),
KEY `expiration_date` (`expiration_date`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=181332 DEFAULT CHARSET=utf8;
enter code here
Best Answer
From the sake of this question, I will call the source table
aaronlax
PROPOSED SOLUTION
SAMPLE DATA
SAMPLE DATA LOADED
mysql>
PROPOSED SOLUTION EXECUTED
GIVE IT A TRY !!!
CAVEAT
In case you are wondering why the proposed solution works, let's take a look at the subqueries
INNERMOST SUBQUERY
NEXT LEVEL SUBQUERY
From this level, I only need three columns:
entry_id
,url_title
,ndx
in the outer most levelThen, I append to the
url_title
a hyphen andndx
ifndx
is nonzero