MySQL VALUES() Function in ON DUPLICATE KEY UPDATE

In MySQL, VALUES(column_name) is a function used in an INSERT ... ON DUPLICATE KEY UPDATE clause to refer to the value that the INSERT would have supplied for a column. The function is deprecated beginning with MySQL 8.0.20. Use a row alias, or row and column aliases, for new SQL instead. See the MySQL INSERT ... ON DUPLICATE KEY UPDATE reference and the MySQL 8.0.20 release notes.

How VALUES(column) works in an upsert

Suppose daily_totals.account_id is a primary key. This statement inserts a new total, or adds the incoming total to the existing row when the key already exists:

CREATE TABLE daily_totals (
    account_id BIGINT PRIMARY KEY,
    total DECIMAL(12, 2) NOT NULL
);

INSERT INTO daily_totals (account_id, total)
VALUES (7, 10.00)
ON DUPLICATE KEY UPDATE total = total + VALUES(total);

Inside the update clause, VALUES(total) means the incoming 10.00. It does not mean the total already stored in the row. If account_id = 7 already has total = 5.00, the update sets it to 15.00.

Replace VALUES() with a row alias

From MySQL 8.0.19, give the row being inserted an alias after the VALUES list, then refer to its column values through that alias:

INSERT INTO daily_totals (account_id, total)
VALUES (7, 10.00) AS incoming
ON DUPLICATE KEY UPDATE total = total + incoming.total;

For multiple inserted rows, the same alias refers to the row that conflicts with a unique key:

INSERT INTO daily_totals (account_id, total)
VALUES (7, 10.00), (8, 4.50) AS incoming
ON DUPLICATE KEY UPDATE total = total + incoming.total;

You can also assign column aliases to the incoming row and use them directly in the update clause:

INSERT INTO daily_totals (account_id, total)
VALUES (7, 10.00) AS incoming(id, new_total)
ON DUPLICATE KEY UPDATE total = total + new_total;

The row alias is required when column aliases are specified. The alias list follows the target columns in the INSERT column list. Check the target MySQL version before using aliases in deployment SQL; they were added in MySQL 8.0.19.

Deprecation and other VALUES syntax

MySQL deprecates VALUES(column_name) in ON DUPLICATE KEY UPDATE starting with MySQL 8.0.20; current MySQL manuals still mark it as deprecated and subject to removal. Replace it with a row alias to avoid deprecation warnings and prepare for a future removal.

This function is separate from the standalone MySQL VALUES statement, which returns rows as a table using VALUES ROW(...). It is also different from the VALUES (...) list used by an INSERT statement. Outside an INSERT value list or its ON DUPLICATE KEY UPDATE clause, VALUES(column_name) returns NULL.

For more examples and constraints, see MySQL’s ON DUPLICATE KEY UPDATE documentation.