Say a table exists,

Is there anyway to write an upsert statement like

INSERT INTO table (name, val) VALUES (x, y) ON DUPLICATE KEY UPDATE val = currentValueInTable   y;

CodePudding user response：

There is no problem referring to the current value in the ON DUPLICATE UPDATE clause. If you are trying to make it automatically get the value from the VALUES clause to add, then there is an old way to do it using the values function:

INSERT INTO `table` (name, val) VALUES ('Mark',4),('Sally',5)
ON DUPLICATE KEY UPDATE val = val   values(val);

This is supported through version 5.7. In version 8, it gives a deprecation warning, but still works. In version 8, the preferred way is to give the VALUES clause an alias and refer to the value using that:

INSERT INTO `table` (name, val) VALUES ('Sally',6),('Jane',7) AS newvalues
ON DUPLICATE KEY UPDATE val = table.val   newvalues.val;

fiddle

Note that the deprecated values function, because of how it was implemented, can unfortunately be used outside of an ON DUPLICATE KEY UPDATE clause, and returns just returns null:

select a from t where a=values(a)

Even more unfortunately, it still returns null if used in a subquery in an ON DUPLICATE KEY UPDATE clause, making queries like this not work as intended:

INSERT INTO t1 VALUES(1,2) ON DUPLICATE KEY
UPDATE a=(SELECT a FROM t2 WHERE b=VALUES(b));

CodePudding user response：

Just use column name:

INSERT INTO table (name, val) VALUES (x, y) ON DUPLICATE KEY UPDATE val = val   y;