0

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
Parapluie
  • 714
  • 1
  • 7
  • 22

3 Answers3

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

or simply

INSERT INTO dictionary (`term`,`references`) 
VALUES 
('0cm','35-6;'),
('10p','89-12;'),
('16761','491-7;') 
ON DUPLICATE KEY UPDATE 
`references` = CONCAT(`references`, VALUES(references));
Akina
  • 39,301
  • 5
  • 14
  • 25
  • @Barmar Thanks. Removed. – Akina Aug 13 '20 at 20:11
  • Good answer, Akina, Thank you. But Barmar's is just so much cleaner. Keep in mind this may be 1,000s of rows added in one go. Still, the WHEN/THEN is a good strategy. – Parapluie Aug 13 '20 at 20:32
  • @Parapluie CASE technique is more general - sometimes the value assigned in ODKU may be non-dependent by the value inserted. VALUES() usage is a special case (but, of course, the most frequent). That is why I give both variants. – Akina Aug 14 '20 at 04:56
  • Understood. It's nice to have that variant. – Parapluie Aug 14 '20 at 15:09
1

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?

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Barmar plus Bill Karwin giving me obvious hints? I am listening! I'll give that a good read. But for the exercise and hopefully the edification of the community, I will see this through first. I will add my update here. Thanks. – Parapluie Aug 13 '20 at 20:20
  • Bingo. That did it. I did not know the correct way to reference the `VALUEs` currently in the `reference` field. Thank you for this lesson. Btw, "reference" must be a restricted term, and would not work unless it was back-ticked in each case: ``references``. Thanks again. – Parapluie Aug 13 '20 at 20:31
  • Yes, `references` is used when declaring foreign keys. – Barmar Aug 13 '20 at 20:33
0

I suspect that you want:

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

This appends to column reference when a duplicate term is given. Note that concat_ws() properly handles the edge case where références is initially null.

GMB
  • 216,147
  • 25
  • 84
  • 135