The Ultimate Guide to High Availability with MariaDB

VALUES

You are viewing an old version of this article. View the current version here.

Syntax

MariaDB 10.3 and later:

VALUE(col_name)

MariaDB 10.2 and before:

VALUES(col_name)

Description

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.

The VALUES() function is meaningful only in INSERT ... ON DUPLICATE KEY UPDATE statements and returns NULL otherwise.

In MariaDB 10.3, this function was renamed to VALUE(), as MariaDB 10.3 supports Table Value Constructors, rendering the old grammar incompatible.

Examples

MariaDB 10.3 and after:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);

MariaDB 10.2 and before:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.