mysql - deleting duplicates in sql and modifying relationship table accordingly -

mysql - deleting duplicates in sql and modifying relationship table accordingly -

i have 3 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 ---> foreign key menu_id (pk in menu_tab)), item_tab_item_id --- foreign key item_id (pk in item_tab))4

there 2 kinds of duplicates encountered 1)item duplicate in same menu_description 2)item duplicate in different menu description

example: 2 chicken sandwiches in lunch menu. 1 chicken sandwich in lunch , 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 replace 3 2............................6 3............................7 3............................8 replace 1 3............................9

how update menu_has_item replaced values after removing duplicates?

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

mysql sql plsql mysql-workbench