The MySQL INSERT statement has the syntax "INSERT ... ON DUPLICATE KEY UPDATE". It's much more convenient than writing separate INSERT and UPDATE statements if you want to insert or update records if they are duplicated. This article summarizes how to use such "INSERT ... ON DUPLICATE KEY UPDATE", useful points, and other specifications.
Use a table like the one below.
select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
CREATE TABLE users (
id INT(8) NOT NULL,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users (id, name, age) VALUES(1, 'taro', 20), (2, 'jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 18 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
+----+------+------+----------------------+---------------------+
For example, suppose you want to add a record with id: 2 to this table, but if you already have that record, you want to rewrite its contents. First check if the record with id: "2" exists, if it does not exist, you will insert an INSERT, and if it does, you will submit an UPDATE statement. The required SQL is as follows.
SELECT * FROM users where id = 2;
--If no result is returned
INSERT INTO users (id, name, age) values (2, 'jiro', 19);
--When the result is returned
UPDATE users SET age = 19 where id = 2;
Let's try constructing and executing this query from Java using placeholders.
//Get Connection instance connection with MySQL
String sqlSelect = "SELECT * FROM users where id = ?;";
PreparedStatement psSelect = connection.prepareStatement(sqlSelect);
psSelect.setInt(1, 2);
ResultSet rs = psSelect.excuteQuery();
if(!rs.next()){
String sqlInsert = "INSERT INTO users (id, name, age) values (?, ?, ?);";
PreparedStatement psInsert = connection.prepareStatement(sqlInsert);
psInsert.setInt(1, 2);
psInsert.setString(2, "jiro");
psInsert.setInt(3, 19);
psInsert.excute();
} else {
String sqlUpdate = "UPDATE users SET age = ? where id = ?;";
PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);
psUpdate.setInt(1, 19);
psUpdate.setInt(2, 2);
psUpdate.excute();
}
connection.commit();
It's quite annoying.
Because "INSERT ... ON DUPLICATE KEY UPDATE" does these conditional branches on the query,
--Whether there is a target record (SELECT statement) --Insert if not (INSERT statement) --Update if there is (UPDATE statement)
It brings together the three queries of.
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = 19;
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
+----+------+------+----------------------+---------------------+
In this case, since the id column is the primary key, if there is a duplicate id in the "INSERT INTO" part, that record will be updated automatically. When this is assembled from Java
String sql = "INSERT INTO users (id, name, age) values (?, ?, ?) "+
"ON DUPLICATE KEY UPDATE age = ?;";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 2);
ps.setString(2, "jiro");
ps.setInt(3, 19);
ps.setInt(4, 2);
ps.excute();
connection.commit();
It's definitely easier.
In addition, a function called VALUES (), which can only be used with "ON DUPLICATE KEY UPDATE", makes query construction easier. You can use this function as follows to refer to the value you tried to insert in the INSERT part.
-- VALUES(age)Returns 19
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
In addition, this function allows you to insert / update multiple rows with "ON DUPLICATE KEY UPDATE".
-- VALUES(age)Is id:"1"In the line of 20, id:"3"Returns 17 on the line
INSERT INTO users (id, name, age) values (1, 'taro', 20), (3, 'saburo', 17) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
As mentioned above, if there is a duplicate primary key, it can be updated according to the age value that you tried to insert in each record.
Also, as you can see here, when you try to execute UPDATE due to duplicate keys, if the value you tried to update and all the existing values match, UPDATE is not executed and "ON UPDATE" is added. The column is also not updated. (Same as the specifications of UPDATE itself)
If you try to construct a query programmatically without using this operation "ON DUPLICATE KEY UPDATE", you will have to loop the pattern itself, which creates a conditional branch using the SELECT statement above, multiple times. However, if you use the VALUES () function with "ON DUPLICATE KEY UPDATE", you only have to loop the operation of assigning to the placeholder as shown below.
int numRecord = 2;
List<String> placeholderList = new ArrayList<>();
for (int i = 0; i < numRecord; i++){
placeholdersList.add("(?, ?, ?)");
}
String sql = "INSERT INTO users (id, name, age) values "+
StringUtils.join(placeholdersList, ", ") +
"ON DUPLICATE KEY UPDATE age = VALUES(age)";
PreparedStatement ps = connection.prepareStatement(sql);
//You only have to loop this part for the number of records
//First record
ps.setInt(1, 1);
ps.setString(2, "taro");
ps.setInt(3, 20);
//Second record
ps.setInt(5, 3);
ps.setString(6, "saburo");
ps.setInt(7, 17);
ps.excute();
connection.commit();
The operation of switching between INSERT and UPDATE by checking if each record already exists in the database can now be performed with just one query.
[IF / CASE control flow functions](https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html] with "ON DUPLICATE KEY UPDATE" syntax ) Can be used for conditional updates.
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--The value of age you tried to insert(VALUES(age))Updated only if is greater than the original age value
INSERT INTO users (id, name, age) values (2, 'jiro', 20), (3, 'sabro', 17) ON DUPLICATE KEY UPDATE age = IF(VALUES(age) > age, VALUES(age), age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:07:38 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--The value of age you tried to insert(VALUES(age))Adds 1 if is greater than the original age value, remains the same if equal, and updates to the new value if smaller
INSERT INTO users (id, name, age) values (2, 'jiro', 22), (3, 'saburo', 15)
ON DUPLICATE KEY UPDATE
age =
CASE
WHEN VALUES(age) > age THEN age + 1
WHEN VALUES(age) = age THEN age
WHEN VALUES(age) < age THEN VALUES(age)
END;
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 21 | 2020-06-05 11:05:52 | 2020-06-05 11:08:06 |
| 3 | saburo | 15 | 2020-06-05 11:07:01 | 2020-06-05 11:08:06 |
+----+--------+------+----------------------+---------------------+
Article that states that if the table has an automatic numbering (AUTO_INCREMENT) column, the serial number will advance by one even if UPDATE is executed without updating when using the "ON DUPLICATE KEY UPDATE" syntax. Often comes out. (Advantages and precautions of INSERT ON DUPLICATE KEY UPDATE etc.)
But in the MySQL version 5.6 reference
(These effects are not the same for InnoDB tables where a is an auto-increment column. If you use an auto-increment column, the INSERT statement increases the auto-increment value, but not UPDATE.)
There is [^ 1].
[^ 1]: The version 8 reference I'm using also says "(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-" It says "increment value but UPDATE does not.)".
I tried to verify.
CREATE TABLE users_increment (
id INT(8) NOT NULL AUTO_INCREMENT,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users_increment (name, age) VALUES('taro', 20), ('jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 18 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
+----+------+------+----------------------+---------------------+
--The INSERT statement is not executed because the key is duplicated
--If the automatic serial number advances even if it is UPDATEd, the id of the next INSERTed record is"4"Should be
INSERT INTO users_increment (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
+----+------+------+----------------------+---------------------+
--The next record actually inserted is"3"
INSERT INTO users_increment (name, age) values ('saburo', 17);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
| 3 | saburo | 17 | 2020-06-05 11:19:46 | 2020-06-05 11:19:46 |
+----+--------+------+----------------------+---------------------+
As mentioned above, in recent versions of MySQL, even if "ON DUPLICATE KEY UPDATE" is performed, the automatic serial number will not proceed unless it is INSERTed.
MySQL 5.6 Reference Manual Control Flow Functions
MySQL 5.6 Reference Manual INSERT ... ON DUPLICATE KEY UPDATE Syntax
MySQL: INSERT ... ON DUPLICATE KEY UPDATE Summary
Advantages and precautions of INSERT ON DUPLICATE KEY UPDATE