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

问题描述

我有三个表: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 encountered1)Item duplicate in the same menu_description2)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中的重复项并相应地修改关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！