MySQL ON DUPLICATE KEY UPDATE and CONCAT

Question

I have a query (Code is the PRIMARY KEY):

INSERT INTO table (Code, ... events)
  VALUES
    (1, ... CONCAT(events, 'BAR')),
    (2, ... CONCAT(events, 'BAR')),
    ...
  ON DUPLICATE KEY
    UPDATE ... events = VALUES(events)

My intention is that the events value being inserted is concatenated to the existing value if there's already a row for the given key.

With my query, assuming the existing value of events is FOO, the new events values are always 'BAR' instead of 'FOOBAR'.

What is the correct way to achieve what I want?

`events = VALUES(events)` where do you concatenate the old value? — Caramiriel, Feb 05 '18 at 11:36
CONCAT(events, '\nEDIT') events will return null at this point so insert will be null to events. — P.Salmon, Feb 05 '18 at 11:43

score 7 · Accepted Answer · edited May 09 '19 at 18:47

To concatenate the new value to the old, move the CONCAT call into the UPDATE:

INSERT INTO table (Code, ... events)
  VALUES
    (1, ... 'BAR'), -- insert plain values
    (2, ... 'BAR'),
    ...
  ON DUPLICATE KEY
    UPDATE ... events = CONCAT(IFNULL(events, ''), VALUES(events)) -- concat if key same

One note about using IFNULL(). If your column events allows NULL values, it happens that result after update will be NULL again if existing value is NULL already. To prevent this, we initialize value by using IFNULL(events, '') before appending your additional data.

MySQL ON DUPLICATE KEY UPDATE and CONCAT

1 Answers1