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

本文介绍了删除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 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;