如何在innodb表中合并2行 [英] How can I merge 2 rows in an innodb table

问题描述

我接管了一个使用mysql innodb数据库的Web应用程序的开发。客户端在事故数据库中有很多重复记录。我正在构建一个合并工具来保留一个记录，并将其他的相关数据推送给它。

I've taken over the development of a web app that uses a mysql innodb database. The client has a lot of duplicate records in the db on accident. I'm building a merge tool to keep one record and push the other's associated data to it.

所以，例如因为打字错误，我可能会有。 p>

So, for example because of a typo, I might have.

entity_id    entity_cat    common_name
--------------------------------------
abcdefg      customer      John Doe
hijklmn      customer      Jon Doe

然后我有一堆链接到entity_id的表。我想删除'hijklmn'，并将其他表中的所有关联数据更改为'customer_entity_id'为'abcdef'。

I then have a bunch of tables that are linked to entity_id. I want to delete 'hijklmn', and have all of the associated data in other tables change their 'customer_entity_id' to 'abcdef'.

问题是，所有关系都删除级联。有没有办法合并这两个记录而不丢失任何数据？

The problem is, the relationships all have on delete cascade. Is there any way for me to merge these two records without losing any data?

推荐答案

关于你的 ON DELETE CASCADE 问题，您可以使用以下脚本模板临时禁用所有外键约束，方法如下：

With regards to your ON DELETE CASCADE issue, you can temporarily disable all foreign key constraints for the purpose of your query with the script template below:

ALTER TABLE `entities` NOCHECK CONSTRAINT ALL

-- commit query here

ALTER TABLE `entities` CHECK CONSTRAINT ALL

这篇关于如何在innodb表中合并2行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！