删除sql中的重复项并相应地修改关系表 [英] deleting duplicates in sql and modifying relationship table accordingly

问题描述

我有三个表: menu_tab具有列(menu_id，menu_description) item_tab具有列(item_id，item_name，item_description，item_price) menu_has_item具有列{(menu_tab_menu_id --->是menu_id的外键(menu_tab中的pk))，item_tab_item_id ---是item_id的外键(item_tab中的pk))4

I have three tables: menu_tab has columns (menu_id,menu_description) item_tab has columns (item_id,item_name,item_description,item_price) menu_has_item has columns{ (menu_tab_menu_id ---> which is foreign key to menu_id (pk in menu_tab)), item_tab_item_id --- which is foreign key to item_id (pk in item_tab))4

将遇到2种重复项 1)项目在相同的menu_description中重复 2)在不同的菜单描述中重复的项目

there will be 2 kinds of duplicates which will be encountered 1)Item duplicate in the same menu_description 2)Item duplicate in a different menu description

示例:午餐菜单中的两个鸡肉三明治.午餐中一份鸡肉三明治，晚餐中另一份鸡肉三明治_description

Example: Two Chicken Sandwiches in the lunch menu. One Chicken Sandwich in Lunch and another in Dinner menu _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 item_tab_item_id
1 ..................................... 1
1 ..................................... 2
1 ..................................... 3
1 ..................................... 4
2 ..................................... 5替换为3
2 ..................................... 6
3 ..................................... 7
3 ............................ 8替换为1
3 ............................ 9

menu_tab_menu_id item_tab_item_id
1............................1
1............................2
1............................3
1............................4
2............................5 replace by 3
2............................6
3............................7
3............................8 replace by 1
3............................9

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

How do I update my menu_has_item with the replaced values after removing the duplicates?

推荐答案

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;

这篇关于删除sql中的重复项并相应地修改关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！