I am using magento 2.4.5 and over the last couple years my product_id’s have changed a couple times as i use a stock import tool and sometimes i have needed to do a complete reimport instead of a merge. Obviously this means my products get new product_id assigned so the little table in the admin panel now shows some products multiples times as bestsellers due to different product_ids. How do i change this in the database to correct the product_ids?
If i try changing product_id in sales_bestsellers_aggregated_daily sales_bestsellers_aggregated_monthly or sales_bestsellers_aggregated_yearly i get the error #1062 - Duplicate entry '2020-01-01-0-33844' for key 'sales_bestsellers_aggregated_yearly.SALES_BESTSELLERS_AGGREGATED_YEARLY_PERIOD_STORE_ID_PRODUCT_ID' for example.
Is this the correct table and is there a way around this? If its the wrong table, what is the correct table? All products have been edited in sales_order_items and i have a grid on my homepage showing what i assume is the correct bestsellers.
Can i alter the quantity in these tables and remove the incorrect product_id entries?