Append duplicate records with count (mysql)

Question

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

Please show the actual table structure using SHOW CREATE TABLE mytable\G — RolandoMySQLDBA
– RolandoMySQLDBA, Commented Nov 3, 2014 at 17:37

RolandoMySQLDBA · Accepted Answer · 2014-11-03 20:16:34Z

From the sake of this question, I will call the source table aaronlax

PROPOSED SOLUTION

SET @ndx = 0;
SET @cur_hash = '';
SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
FROM (SELECT
   @new_hash := MD5(url_title),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
   @cur_hash := MD5(url_title),
   url_title,entry_id
FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;

SAMPLE DATA

use test
DROP TABLE IF EXISTS aaronlax;
CREATE TABLE aaronlax
(
    entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    url_title varchar(75) NOT NULL,
    PRIMARY KEY (entry_id),
    KEY (url_title)    
) ENGINE=MyISAM;
INSERt INTO aaronlax (url_title) VALUES
('a-stoney'),('a-stoney'),
('ab-dille'),('ab-dille'),
('ab-dille'),('ac-gifta'),
('ae-jones'),('ae-jones');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS aaronlax;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE aaronlax
    -> (
    ->     entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->     url_title varchar(75) NOT NULL,
    ->     PRIMARY KEY (entry_id),
    ->     KEY (url_title)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERt INTO aaronlax (url_title) VALUES
    -> ('a-stoney'),('a-stoney'),
    -> ('ab-dille'),('ab-dille'),
    -> ('ab-dille'),('ac-gifta'),
    -> ('ae-jones'),('ae-jones');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from aaronlax;
+----------+-----------+
| entry_id | url_title |
+----------+-----------+
|        1 | a-stoney  |
|        2 | a-stoney  |
|        3 | ab-dille  |
|        4 | ab-dille  |
|        5 | ab-dille  |
|        6 | ac-gifta  |
|        7 | ae-jones  |
|        8 | ae-jones  |
+----------+-----------+
8 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_hash = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
    -> FROM (SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;
+----------+------------+
| entry_id | url_title  |
+----------+------------+
|        1 | a-stoney   |
|        2 | a-stoney-1 |
|        3 | ab-dille   |
|        4 | ab-dille-1 |
|        5 | ab-dille-2 |
|        6 | ac-gifta   |
|        7 | ae-jones   |
|        8 | ae-jones-1 |
+----------+------------+
8 rows in set (0.00 sec)

mysql>

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

mysql> SELECT url_title,entry_id FROM aaronlax ORDER BY url_title;
+-----------+----------+
| url_title | entry_id |
+-----------+----------+
| a-stoney  |        1 |
| a-stoney  |        2 |
| ab-dille  |        3 |
| ab-dille  |        4 |
| ab-dille  |        5 |
| ac-gifta  |        6 |
| ae-jones  |        7 |
| ae-jones  |        8 |
+-----------+----------+
8 rows in set (0.00 sec)

mysql>

NEXT LEVEL SUBQUERY

mysql> SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA;
+----------------------------------+------+----------------------------------+-----------+----------+
| @new_hash := MD5(url_title)      | ndx  | @cur_hash := MD5(url_title)      | url_title | entry_id |
+----------------------------------+------+----------------------------------+-----------+----------+
| 911adc6db360f218da5069b20cfb1917 |    0 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        1 |
| 911adc6db360f218da5069b20cfb1917 |    1 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        2 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    0 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        3 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    1 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        4 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    2 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        5 |
| 3fff0367af8bf5d3b3b0089b8f187c04 |    0 | 3fff0367af8bf5d3b3b0089b8f187c04 | ac-gifta  |        6 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    0 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        7 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    1 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        8 |
+----------------------------------+------+----------------------------------+-----------+----------+
8 rows in set (0.00 sec)

mysql>

From this level, I only need three columns: entry_id, url_title, ndx in the outer most level

Then, I append to the url_title a hyphen and ndx if ndx is nonzero

As a SELECT this works PERFECTLY, can you recommend an adjustment to run this as an UPDATE so the existing table is set to reflect the corrected +ndx output? — Aaron Lax
– Aaron Lax, Commented Nov 3, 2014 at 21:55
Same question here, did anybody figure this out as an update already @AaronLax — Alex
– Alex, Commented Apr 30, 2021 at 7:54

Dario Seidl · Accepted Answer · 2023-01-15 13:06:38Z

Two people asked in comments to the other answer, how to turn this select into an update statement.

It's pretty straightforward, with an update on the original table joined with the table created by the select statement:

SET @ndx = 0;
SET @cur_hash = '';

UPDATE aaronlax
JOIN (

SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
FROM (SELECT
   @new_hash := MD5(url_title),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
   @cur_hash := MD5(url_title),
   url_title,entry_id
FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A

) U

ON aaronlax.entry_id = U.entry_id
SET aaronlax.url_title = U.url_title

Stack Exchange Network

Append duplicate records with count (mysql)

2 Answers 2

PROPOSED SOLUTION

SAMPLE DATA

SAMPLE DATA LOADED

PROPOSED SOLUTION EXECUTED

GIVE IT A TRY !!!

CAVEAT

INNERMOST SUBQUERY

NEXT LEVEL SUBQUERY

Your Answer

Hot Network Questions

Append duplicate records with count (mysql)

2 Answers 2

PROPOSED SOLUTION

SAMPLE DATA

SAMPLE DATA LOADED

PROPOSED SOLUTION EXECUTED

GIVE IT A TRY !!!

CAVEAT

INNERMOST SUBQUERY

NEXT LEVEL SUBQUERY

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions