MySQL/MariaDB ON DUPLICATE KEY UPDATE based on condition

Question

I want to use a conditional

ON DUPLICATE KEY UPDATE

Based on the example provided in this question, suppose name is the primary key. We want to execute the following query:

INSERT INTO beautiful (name, age, col3, col 4, ..., col 100)
    VALUES
    ('Helen', 24, ...),
    ('Katrina', 21, ...),
    ('Samia', 22, ...),
    ('Hui Ling', 25, ...),
    ('Yumie', 29, ...)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
    col3= VALUES(col3),
    col4= VALUES(col4),
     ...
    col100= VALUES(col100)

And (in MariaDB) I want the update to be done, only when the age of the newly received record is larger than the one already existent in the database.

Is there a way to do this?

UPDATE: Updated to reflect the fact that each record has multiple fields

Answer 1

Here's an example:

MariaDB [sandbox]> create table t(name varchar(20),age int default 0 , primary key(name));
Query OK, 0 rows affected (0.28 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO t (name, age)
    ->     VALUES
    ->     ('Helen', 24),
    ->     ('Katrina', 21),
    ->     ('Samia', 22),
    ->     ('Hui Ling', 25),
    ->     ('Yumie', 29)
    -> ON DUPLICATE KEY UPDATE
    ->     age = if(VALUES(age) > age,values(age),age);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from t;
+----------+------+
| name     | age  |
+----------+------+
| Helen    |   24 |
| Hui Ling |   25 |
| Katrina  |   21 |
| Samia    |   22 |
| Yumie    |   29 |
+----------+------+
5 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO t (name, age)
    ->     VALUES
    ->     ('Helen', 25),
    ->     ('Katrina', 21),
    ->     ('Samia', 22),
    ->     ('Hui Ling', 25),
    ->     ('Yumie', 29)
    -> ON DUPLICATE KEY UPDATE
    ->     age = if(VALUES(age) > age,values(age),age);
Query OK, 2 rows affected (0.02 sec)
Records: 5  Duplicates: 1  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from t;
+----------+------+
| name     | age  |
+----------+------+
| Helen    |   25 |
| Hui Ling |   25 |
| Katrina  |   21 |
| Samia    |   22 |
| Yumie    |   29 |
+----------+------+
5 rows in set (0.00 sec)

If there are n columns which update when age changes, then:

drop table if exists t;

create table t(name varchar(20),age int default 0 , col1 int, col2 int,col3 int,primary key(name));
INSERT INTO t (name, age, col1,col2,col3)
 VALUES
      ('Helen', 24,1,1,1),
      ('Katrina', 21,1,1,1),
      ('Samia', 22,1,1,1),
      ('Hui Ling', 25,1,1,1),
      ('Yumie', 29,1,1,1)
  ON DUPLICATE KEY UPDATE
     col1 = if(VALUES(age) > age,values(col1),col1),
     col2 = if(VALUES(age) > age,values(col2),col2),
     col3 = if(VALUES(age) > age,values(col3),col3),
     age = if(VALUES(age) > age,values(age),age);
     
select * from t;
INSERT INTO t (name, age, col1,col2,col3)
 VALUES
      ('Helen', 25,2,2,2),
      ('Katrina', 21,2,2,2),
      ('Samia', 22,1,1,1),
      ('Hui Ling', 25,1,1,1),
      ('Yumie', 29,1,1,1)
  ON DUPLICATE KEY UPDATE
      col1 = if(VALUES(age) > age,values(col1),col1),
     col2 = if(VALUES(age) > age,values(col2),col2),
     col3 = if(VALUES(age) > age,values(col3),col3),
     age = if(VALUES(age) > age,values(age),age);
     
select * from t;

MariaDB [sandbox]> select * from t;
+----------+------+------+------+------+
| name     | age  | col1 | col2 | col3 |
+----------+------+------+------+------+
| Helen    |   25 |    2 |    2 |    2 |
| Hui Ling |   25 |    1 |    1 |    1 |
| Katrina  |   21 |    1 |    1 |    1 |
| Samia    |   22 |    1 |    1 |    1 |
| Yumie    |   29 |    1 |    1 |    1 |
+----------+------+------+------+------+
5 rows in set (0.00 sec)

Note age has to be last updated.

There is no shortcut to keying all the updateable columns. If the columns other than age are dynamic then it may be worth looking at dynamic SQL.

Another approach might be to load the inserts to a staging table with a trigger to update your master table.