Example Tables

Below are some example tables to be used below. You can see that we can easily do bulk inserts in MySQL using INSERT ... VALUES.

CREATE TABLE example_users (
	user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
	full_name TEXT NOT NULL,
	value_to_update INT UNSIGNED
) ENGINE=InnoDB;
INSERT INTO example_users (user_id, full_name)
VALUES (1, 'Amy'), (2, 'Bob'), (3, 'Carol');

CREATE TABLE example_unique_rows (
	row_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
	unique_field VARCHAR(32) NOT NULL,
	value_to_update INT UNSIGNED,
	UNIQUE INDEX(unique_field)
) ENGINE=InnoDB;
INSERT INTO example_unique_rows (row_id, unique_field)
VALUES (1, 'Field 1'), (2, 'Field 2'), (3, 'Field 3');

INSERT ... ON DUPLICATE KEY UPDATE Method

By using and INSERT with ON DUPLICATE KEY UPDATE (documentation at https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html), you can do a bulk insert like above knowing that it’s really going to be doing updates. In the ON DUPLICATE KEY UPDATE part, add field_name = VALUES(field_name) for each field you want to update.

INSERT INTO example_users (user_id, full_name, value_to_update)
VALUES (1, 'Amanda', 100), (2, 'Bill', 200), (3, 'Clare', 300)
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name), value_to_update = VALUES(value_to_update);

At this point, the table will look like this:

user_id	full_name	value_to_update
1	Amanda	100
2	Bill	200
3	Clare	300

Caveats

However, there are issues with this approach. First, you must specify all non-null columns. Something like the following will result in an error like:
ERROR 1364 (HY000): Field 'full_name' doesn't have a default value

INSERT INTO example_users (user_id, value_to_update)
VALUES (1, 100), (2, 200), (3, 300)
ON DUPLICATE KEY UPDATE value_to_update = VALUES(value_to_update);

Secondly, you can run into unexpected situations with unique keys. The ON DUPLICATE KEY UPDATE check will be for any key, not just the primary key. Consider the following update, which attempts to update the rows, but also add a fourth row that has a duplicate unique_field value.

INSERT INTO example_unique_rows (row_id, unique_field, value_to_update)
VALUES (1, 'Field 1', 100), (2, 'Field 2', 200), (3, 'Field 3', 300), (4, 'Field 3', 400)
ON DUPLICATE KEY UPDATE unique_field = VALUES(unique_field), value_to_update = VALUES(value_to_update);

At this point, the table looks like the following, which is likely not expected. Row #3 has a value of 400 and there is no row #4.

row_id	unique_field	value_to_update
1	Field 1	100
2	Field 2	200
3	Field 3	400

The last issue with this method is that as of MySQL 8.0.20, using VALUES is deprecated. This can be solved in MySQL 8.0.19 or higher by using the AS new syntax below, but it's likely better to use an UPDATE as described in the next section.

INSERT INTO example_users (user_id, full_name, value_to_update)
VALUES (1, 'Amanda', 100), (2, 'Bill', 200), (3, 'Clare', 300) AS new
ON DUPLICATE KEY UPDATE full_name = new.full_name, value_to_update = new.value_to_update;

UPDATE with JOIN on VALUES Method (MySQL 8.0.19+)

With MySQL 8.0.19 and higher, there is a better option available for bulk updates. This makes use of the VALUES statement (documentation at https://dev.mysql.com/doc/refman/8.0/en/values.html). The VALUES statement can include a list of ROWs that can be used to list the primary key and the new values of the fields to be updated. The fields in each ROW can be references by using column_0, column_1, column_2, etc. You can JOIN the table you wish to update with this temporary table to do the updates. An example is below.

UPDATE example_users t1
JOIN (
	VALUES ROW(1, 'Andrew', 111), ROW(2, 'Brianna', 222), ROW(3, 'Colton', 333)
) t2 ON t1.user_id = t2.column_0
SET t1.full_name = t2.column_1, t1.value_to_update = t2.column_2;

There are several advantages of this over the ON DUPLICATE KEY method above.
1. You only need to specify the primary key column and the columns you wish to update.
2. It avoids the issue of non-primary key unique keys.
3. It will not add a new row if the row does not exist.