删除sql中的重复项并相应地修改关系表

Question

我有三个表：menu_tab有列（menu_id，menudescription）item_tab有列（item_id、item_name、itemdescription、item_price）menu_has_item具有列｛（menu_tab_menu_id--->，它是menu_id的外键（menu_tab中的pk）），item_tab_item_id---，它是item_id的主键（item_tab中pk）4

将遇到两种重复1） 同一菜单中的项目重复description2） 不同菜单描述中的项目重复

示例：午餐菜单上有两个鸡肉三明治。午餐中的一个鸡肉三明治和晚餐菜单中的另一个description

menu_tab    
menu_id menu_description
1        lunch
2        dinner
3        Specials

item_tab        
item_id item_description    
1       b 
2       d   
3       g   
4       x   
5       g          delete g
6       d   
7       e   
8       b          delete b
9       x   

menu_has_tab

menu_tab_menu_id项目_tab_item_id
1…………..1
1…………..2
1…………..3
1…………..4
2…………..5替换为3
2…………..6
3…………..7
3…………..8替换为1
3…………9

删除重复项后，如何使用替换的值更新menu_has_item？

Answer 1

begin
  for x in (
            -- find duplicate items
            select *
              from (select rowid row_id,
                           item_id,
                           item_description,
                           row_number() over(partition by item_description order by
                           item_description) row_no
                       from item_tab)
            where row_no > 1) loop
-- replaceing duplicate Items
    update menu_has_item 
    set menu_has_item.item_tab_item_id =
           ( select item_id
              from (select item_id,
                           row_number() over(partition by item_description order by
                           item_description) row_no
                       from item_tab where 
                       item_tab.item_description = x.item_description)
             where row_no = 1)
   where menu_has_item .item_tab_item_id = x.item_id;
-- deleting duplicate items
     delete item_tab where rowid = x.row_id;
  end loop;
-- commit;
end;

Answer 2

首先，您需要用新值替换menu_tab中的重复项

merge into menu_tab dest
using (select *
         from (select item_id, min(item_id) over(partition by item_description) as new_item_id from item_tab)
        where item_id != new_item_id) src
on (dest.item_tab_item_id = src.item_id)
when matched then
    update set dest.item_tab_item_id = new_item_id;

之后，您需要从项目表中删除重复项你可以在那里找到脚本http://sprogram.com.ua/en/articles/oracle-delete-duplicate-record

你把这个问题标记为plsql，我误以为你是关于Oracle的，对不起。但我怀疑MySQL中存在merge语句祝好运

Answer 3

我为我的表Rout（RoutID，SourceCityID，DestCityID）和Form（FormID，RoutID…）做了这件事我从Rout表中删除了重复的路由，并更新了Form表中的RoutID
首先获取按列分组的重复行，您要对其进行重复比较

(SELECT * FROM
    Rout,
    (SELECT MIN(RoutID) MinRoutID
    FROM Rout,
        (SELECT SourceCityID,DestCityID
        FROM Rout
        GROUP BY SourceCityID,DestCityID
        HAVING count(*) > 1) AS Duplicates
    WHERE Rout.SourceCityID=Duplicates.SourceCityID AND Rout.DestCityID=Duplicates.DestCityID
    GROUP BY Rout.SourceCityID,Rout.DestCityID)AS MRCols
WHERE RoutID=MinRoutID)AS DuplicateGroup

然后在不分组的情况下获得所有重复的行，并将与列进行比较以获得重复的

(SELECT RoutID,Rout.SourceCityID,Rout.DestCityID FROM Rout,
    (SELECT SourceCityID,DestCityID
    FROM Rout
    GROUP BY SourceCityID,DestCityID
    HAVING count(*) > 1)AS Duplicates
WHERE Rout.SourceCityID=Duplicates.SourceCityID AND Rout.DestCityID=Duplicates.DestCityID)AS DuplicateDetail

然后更新表格tbl如下：

UPDATE Form SET RoutID=DuplicateGroup.RoutID
FROM
    Form,
    (SELECT * FROM
        Rout,
        (SELECT MAX(RoutID) MinRoutID
        FROM Rout,
            (SELECT SourceCityID,DestCityID
            FROM Rout
            GROUP BY SourceCityID,DestCityID
            HAVING count(*) > 1) AS Duplicates
        WHERE Rout.SourceCityID=Duplicates.SourceCityID AND Rout.DestCityID=Duplicates.DestCityID
        GROUP BY Rout.SourceCityID,Rout.DestCityID)AS MRCols
    WHERE RoutID=MinRoutID)AS DuplicateGroup
    ,
    (SELECT RoutID,Rout.SourceCityID,Rout.DestCityID FROM Rout,
        (SELECT SourceCityID,DestCityID
        FROM Rout
        GROUP BY SourceCityID,DestCityID
        HAVING count(*) > 1)AS Duplicates
    WHERE Rout.SourceCityID=Duplicates.SourceCityID AND Rout.DestCityID=Duplicates.DestCityID)AS DuplicateDetail
WHERE
    Form.RoutID=DuplicateDetail.RoutID AND
    DuplicateGroup.SourceCityID=DuplicateDetail.SourceCityID
    AND DuplicateGroup.DestCityID=DuplicateDetail.DestCityID

现在删除Rout中不在Form 表中的行

DELETE FROM Rout WHERE RoutID NOT IN(SELECT DISTINCT RoutID FROM Form)