如何删除重复的行并更新表 [英] How to delete duplicated rows and update the table

问题描述

我在表中有重复的行.

I have duplicated rows in tables.

我有两个通过外键连接的表

I have two table which are connected by a foreign key

regions (id)
orders (region_id)

区域具有重复的名称.我想删除这些重复的行并更新订单表，该表现在会将重复的外键设置为在区域表中仅保留现有名称.

The regions have duplicated names. I want to delete these duplicated rows and update orders table that duplicated foreign key will now be set to only left existing name in regions table.

示例:

regions table:

id  name
1 | test
2 | test
3 | foo

orders table:

id region_id
6 | 1
7 | 2
9 | 3

我想要

orders table:

id region_id
6 | 1
7 | 1
9 | 3

regions table:

id  name
1 | test
3 | foo

我可以使用此SQL获得重复的行:

I can get duplicated rows with this SQL:

SELECT name, count(id) as cnt FROM regions 
GROUP BY name HAVING cnt > 1

我该如何将此选择与订单表连接，并删除重复的行并更新该表?

How can I connect this select with order table and delete duplicated rows and update the table?

推荐答案

要更新订单表，例如:

update  orders
join    regions r1
on      r1.id = orders.region_id
set     orders.region_id = 
        (
        select  min(r2.id)
        from    regions r2
        where   r2.name = r1.name
        )

之后，您可以使用以下方法删除重复的行:

After that, you can delete duplicate rows with:

delete  regions
from    regions
where   id not in
        (
        select  id
        from    (
                select  min(id) as id
                from    regions
                group by
                    name
                ) as SubqueryAlias
        )

必须使用double子查询才能避免MySQL错误ERROR 1093 (HY000) at line 36: You can't specify target table 'regions' for update in FROM clause.

The double subquery is required to avoid the MySQL error ERROR 1093 (HY000) at line 36: You can't specify target table 'regions' for update in FROM clause.

这篇关于如何删除重复的行并更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！