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

score 5 · Answer 1 · edited Mar 24 '23 at 23:04

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.

Thanks, but I actually tried to do it differently since this does not scale if we have 100 columns. We then have to copy `col98 = if(VALUES(age) > age,values(col98),col98);` for all columns — andreas, Mar 29 '18 at 08:55
@andreas Seems like a data/structure issue, and was not mentioned at all in the original question. — Blue, Mar 29 '18 at 08:56
You need to revisit your question describing your data structure. — P.Salmon, Mar 29 '18 at 08:58
@FrankerZ You are correct that it was not mentioned. I just wanted the example I provided to be very minimal just to focus on the fact that I wanted a **conditional** ON DUPLICATE KEY UPDATE — andreas, Mar 29 '18 at 09:00
Let me see if I understand you.. If someone reaches age 21(say) columns 1-5 (for example) should also be allowed to chang , if someone reaches 30(say) columns 4-10 (for example should be allowed to change — P.Salmon, Mar 29 '18 at 11:31
@P.Salmon No, to explain more the "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": If newRecord.age>oldRecord.age then ALL 100 fields should be updated, else NONE field of the rest 100 fields should be updated. — andreas, Mar 29 '18 at 14:14

MySQL/MariaDB ON DUPLICATE KEY UPDATE based on condition

1 Answers1