MySQL bulk INSERT .. ON DUPLICATE KEY UPDATE while specifying individual columns

Question

I need to bulk insert thousands of rows if a table from 2 different cronjobs with different datasets, I'm currently using INSERT .. ON DUPLICATE KEY UPDATE

Considering the first value is the unique key to check for duplicates,

First cronjob would for example run (from data source 1):

INSERT INTO `places` () VALUES
 ('aaa', 'bbb', NULL),
 ('ccc', NULL, 'ddd')
ON DUPLICATE KEY UPDATE;

Second cronjob would for example run (from data source 2):

INSERT INTO `places` () VALUES
 ('aaa', NULL, 'eee'),
 ('ccc', 'ddd', 'fff')
ON DUPLICATE KEY UPDATE

As you can see, the second cronjob would overwrite valid values from the first cronjob with null values, which I don't want.
If I remove the null values from the data, the query would break as there would be a data mismatch.

The only solutions I can think of is to execute LOTS of single insert/updates, which would be slower.

If I could do a bulk

INSERT INTO `places` VALUES
 (column1='aaa', column3='eee'),
 (column1='ccc', column2='ddd', column3='fff')
ON DUPLICATE KEY UPDATE

then I wouldn't have to worry about keys anyone else but I don't think it's possible in MySQL.. does anyone know of a better way to do this?

if you only care about the primary key... just use insert ignore — Jorge Campos, Oct 11 '19 at 18:37
https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Jorge Campos, Oct 11 '19 at 18:39
I can't insert ignore unfortunately. Cronjob 2 might have the data missing from cronjob 1 so it should complete it — NaturalBornCamper, Oct 11 '19 at 18:51

score 2 · Accepted Answer · answered Oct 11 '19 at 18:57

I tested the following and it seems to work:

INSERT INTO `places` (col1, col2, col3) VALUES
 ('aaa', NULL, 'eee'),
 ('ccc', 'ddd', 'fff')
ON DUPLICATE KEY UPDATE
 col1=COALESCE(VALUES(col1), col1),
 col2=COALESCE(VALUES(col2), col2),
 col3=COALESCE(VALUES(col3), col3);

In an ON DUPLICATE KEY statement, the VALUES(<col>) means "whatever I tried to insert in each row." So in the first row above, VALUES(col2) is NULL, and col2 is the value already stored on the row for that primary key.

The COALESCE() function returns its first non-NULL argument. So COALESCE(NULL, col2) returns the existing value for col2.

You do need to write out all the clauses for all these columns if you want to have them "default to previous" in this way.

P.S.: Your use of INSERT INTO places () is incorrect. The empty list of columns () must correspond to an empty list of values. I.e. the two lists must have the same number of terms.

COALESCE!!! How did I not think of that! Thanks friend :) Thanks the extra tip on my mistake! — NaturalBornCamper, Oct 11 '19 at 19:04

MySQL bulk INSERT .. ON DUPLICATE KEY UPDATE while specifying individual columns

1 Answers1