本文介绍了MySQL在重复键上插入行更新多列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个表(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在重复键上插入行更新多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！