MySQL在重复键上插入行更新多列 [英] MySQL insert row on duplicate key update multiple columns

问题描述

我有一个表(name, date, stat1, stat2, stat3)，(name, date)是PK.当我插入行时，将有重复的键，并且我需要总结三个统计信息.我对Java中的PreparedStatement使用以下查询:

I have a table (name, date, stat1, stat2, stat3), (name, date) is the PK. When I insert rows, there will be duplicate keys, and I need to sum up the three stats. I use the following query with PreparedStatement in Java:

INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + ?, stat2 = stat2 + ?, stat3 = stat3 + ?

是否有更简洁的查询来实现?因为我简化了查询，所以那里有十多个统计信息.

Is there a more concise query to achieve that? Because I have simplify the query, there are over ten stats there.

推荐答案

INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1), stat2 = stat2 + VALUES(stat2), stat3 = stat3 + VALUES(stat3)

这篇关于MySQL在重复键上插入行更新多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！