Given a table
CREATE TABLE `hmm` (
`a` int(10) unsigned NOT NULL default '0',
`b` text NOT NULL,
`c` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1;
SELECT * FROM hmm;
+---+--------------------------+---+
| a | b | c |
+---+--------------------------+---+
| 1 | hmm4,hmm5,hmm6,hmm7,hmm8 | 7 |
+---+--------------------------+---+
Let's break apart the INSERT statement, if column 'a' produces a duplicate ON DUPLICATE KEY UPDATE will issue an IF statement
FIND_IN_SET will return the position of the found string. The logic of IF say if FIND_IN_SET is is greater then 0 i.e. the string was found, update column b with b itself (do nothing) else concat the column with the column contents and the new string separated by a comma. Additionally in all cases increment column c by 1.
Why do it this way? Well I removed the need to do a SELECT and provided a solution to store distinct text values instead of the raw value, thus saving space and resources on network transfers etc.
A little quick statement that might be useful for others.