Search code examples
mysqlstringsql-updatesql-inserton-duplicate-key

ON DUPLICATE KEY concatenates *every* value of column

This is a head-scratcher.

I am using CONCAT in the context of ON DUPLICATE KEY UPDATE and it is spitting out very strange results.

Assume that a table exists with two columns and three rows, thus:

term   | reference
0cm    | 49-5;
10p    | 890-1;
11s    | 491-1;
16761  | 768-1;

(Despite the numbers, these are all VARCHAR strings.)

Now assume that we run the following query:

INSERT INTO dictionary (`term`,`references`) 
VALUES 
('0cm','35-6;'),
('10p','89-12;'),
('16761','491-7;') 
ON DUPLICATE KEY UPDATE 
`references` = CONCAT(`references`,'35-6;'),
`references` = CONCAT(`references`,'89-12;'),
`references` = CONCAT(`references`,'491-7;');

Because term is a unique index, ON DUPLICATE KEY becomes the active portion of the script (save for the unaffected 11s). The expected behaviour is that our new values will be appended to the existing values.

The query runs successfully, but gives me these unexpected results:

term   | reference
0cm    | 49-5;35-6;89-12;491-7;
10p    | 890-1;35-6;89-12;491-7;
11s    | 491-1;
16761  | 768-1;35-6;89-12;491-7;

It has concatenated every value of reference.

Is there some way to "clear" the reference reference before ON DUPLICATE KEY performs its UPDATE?

Solution

  • Use VALUES(references) to get the value that was going to be inserted into that row if it didn't have a duplicate key.

    INSERT INTO dictionary (`term`,`references`) 
VALUES 
('0cm','35-6;'),
('10p','89-12;'),
('16761','491-7;') 
ON DUPLICATE KEY UPDATE 
`references` = CONCAT(`references`,VALUES(`references`));

    BTW, you should read this: Is storing a delimited list in a database column really that bad?