重复键更新时的 SQL 更新
我问过这个问题分组和更新大型数据库表，但我没有'没有得到答案。
我有一个表：name、date、detail、no，并且name、date、detail一起作为PK。
不知何故，我需要更新detail，并且可能存在重复的密钥。因此，我需要对重复行的 no 进行求和。 ON DUPLICATE KEY UPDATE 仅用于INSERT。那么如何解决这个问题呢？
I have asked this question Grouping and update large database table, but I didn't get the answer for this.
I have a table: name, date, detail, no, and name, date, detail are together as PK.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，多列主键可能是一个坏主意；正如您所发现的，这使得操纵各个字段变得困难。您应该做的是向该表添加一个自动增量 bigint 列，它将成为您的新主键，并且您的三列唯一性约束可以是唯一索引。它应该表现得更好......但它也将允许您进行所需的操作。它可以让您执行修改，但仍然可以让您通过整数索引来识别原始行。
如果您这样做，只要您不介意创建一些临时表来使用，您的“一次性更新”现在就可以安全地完成。像这样的事情：
创建几个具有相同架构的临时表，但没有唯一三列索引 - 您可以拥有非唯一索引，因为它将有助于您的查询即将表演；
将需要处理的记录复制到第一个表中（包括唯一整数主键）；
更新临时表中所有需要更新的
detail列；使用
INSERT ... SELECT与SUM和GROUP BY将这些记录合并到第二个表中；最后，从原表中删除temp1表中的所有记录（使用整数主键），并将temp2表中的记录插入到原表中。
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
detailcolumns you need to update in the temporary table;Use
INSERT ... SELECTwithSUMandGROUP BYto merge those records into the second table;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.
糟糕的设计。您应该使用代理 id 主键并使这些字段成为复合唯一索引。如果你想稍后引用这个表格，你会得到什么？ 3 个额外字段作为另一个表中的外键和一个超大索引。您将如何更新详细信息字段？如果你之前说过它是一个大表，那就意味着 PK 索引重建。如果可能并且尚未引用约束，请禁用该约束。从源表中选择不同的或分组依据，并使用此选择进行更新。
替换编辑：
Bad design. you should use surrogate id primary key and make these fields a composite unique index. If you want to reference this a table later what will you have? 3 extra fields as a foreign key in another table and an extra big index. How will you update the the detail field? If you said it before it's a big table it means that PK index rebuild. Disable the constraint if possible and if its not referenced already. Make select distinct or group by from your source table and make your update using this select.
REPLACE EDIT: