Conditionally update multiple values without insert-CodePudding

Given a set of data, how do I update all rows matching the same id with values that are greater than what already exist, without inserting any extra rows?

This is my table:

CREATE TABLE `contacts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The table has these rows:

id	date
1	2021-01-01 10:00:00
2	null
3	2019-10-01 10:00:00
4	2019-10-01 10:00:00

The data in my program (json):

[
  {
    "id": 1,
    "date": "2020-01-01 13:00:00"
  },
  {
    "id": 2,
    "date": "2020-01-01 14:00:00"
  },
  {
    "id": 3,
    "date": "2020-01-01 14:00:00"
  },
  {
    "id": 5,
    "date": "2020-01-01 14:00:00"
  }
]

I want to achieve this:

id	date
1	2021-01-01 10:00:00
2	2020-01-01 14:00:00
3	2020-01-01 14:00:00
4	2019-10-01 10:00:00

In the result, rows without matching id should not be inserted. Rows already having a greater date should not be updated.

I am using MySQL 8.0.26

CodePudding user response：

UPDATE contacts
JOIN JSON_TABLE (@source_data,
                 '$[*]' COLUMNS (id INT PATH '$.id',
                                 `date` DATETIME PATH '$.date')) src USING (id)
SET contacts.`date` = CASE WHEN contacts.`date` > src.`date`
                           THEN contacts.`date`
                           ELSE src.`date`
                           END;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a779d637c09197cdc6a2e7c75aef731b

CodePudding user response：

One way to achieve it is to construct a table from the values and join on it. Then use GREATER to update with the greatest value chosen between what already exists on contacts and the constructed table t2.

UPDATE contacts
INNER JOIN (
  SELECT * FROM (VALUES
    ROW(1, '2020-01-01 13:00:00'),
    ROW(2, '2020-01-01 14:00:00'),
    ROW(3, '2020-01-01 14:00:00'),
    ROW(5, '2020-01-01 14:00:00')
  ) AS t1(id, date)
) AS t2 ON contacts.id = t2.id
SET contacts.date = GREATEST(COALESCE(contacts.date, 0), t2.date)

Note this solution only works for MySQL versions greater than 8.0.19, since that is when the VALUES statement was introduced. https://dev.mysql.com/doc/refman/8.0/en/values.html