重复密钥更新的SQL更新 [英] SQL update on duplicate key update

问题描述

我已经提出了这个问题分组和更新大型数据库表，但是我没有得到这个答案。

I have asked this question Grouping and update large database table, but I didn't get the answer for this.

我有一张表：名称，日期，细节，没有 ，而名称，日期，细节一起作为PK。

I have a table: name, date, detail, no, and name, date, detail are together as PK.

不知何故，我需要更新 detail ，可能有重复的键。因此，我需要为重复的行总和 no 。 ON DUPLICATE KEY UPDATE 仅用于 INSERT 。那么如何解决这个问题？

Somehow I need to update detail, and it is possible that there are duplicate key. Thus I need to sum the no for the duplicate rows. ON DUPLICATE KEY UPDATE is only used for INSERT. So how to address this problem?

推荐答案

首先，多列主键可能是一个坏主意;正如你所发现的，它使得难以操纵各个领域。你应该做的是将一个autoincrement bigint列添加到该表中，这将成为你的新主键，而你的三列唯一性约束可以是一个唯一的索引。它应该表现更好...但它也将允许您进行所需的操作，以及。它将允许您进行修改，但仍然可以通过整数索引来确定原始行。

First things first, that multi-column primary key is probably a bad idea; as you've found out, it makes it difficult to manipulate the individual fields. What you ought to do is add an autoincrement bigint column to that table, which will become your new primary key, and your three-column uniqueness constraint can be a unique index instead. It should perform better... but it'll also allow you to do the sort of manipulation you need, as well. It'll let you perform modifications but still let you identify the original rows by their integer index.

如果这样做，您的一次性更新现在可以是只要你不介意创建一些临时表来处理，安全地完成。这样做：

If you do that, your "one-time update" can now be done safely, as long as you don't mind creating some temporary tables to work with. Something like this:

创建一些具有相同模式的临时表，但没有三列索引，您可以拥有非唯一索引，因为它将帮助您即将执行的查询;

Create a couple of temporary tables with the same schema, but without the unique three-column index - you can have a non-unique index, because it'll help the queries you're about to perform;

将要处理的记录复制到第一个表中（包括唯一整数主键）;

Copy the records you need to process into the first table (including the unique integer primary key);

更新您需要在临时表中更新的所有详细信息列;

Update all the detail columns you need to update in the temporary table;

使用 INSERT ... SELECT 与 SUM 和 GROUP BY 将这些记录合并到第二个表中;

Use INSERT ... SELECT with SUM and GROUP BY to merge those records into the second table;

INSERT INTO temp2 (...whatever...) SELECT ...whatever..., SUM(no) FROM temp1 GROUP BY ...whatever...

最后，从原始表（使用整数主键）删除temp1表中的所有记录，并将temp2表中的记录插入原始表。

Finally, delete all the records in the temp1 table from the original table (using the integer primary key), and insert the records in the temp2 table into the original table.

这篇关于重复密钥更新的SQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！