Run Time Error '35602' when trying to run Inventory Stock Status Report [00160320]
/*
Created by: Emeka D
Purpose: This query deletes duplicate records from the table IN_Material_Handler_Warehouses — keeping only the most recent (highest Material_Handler_Warehouse_Id) record for each (Employee_Id, Warehouse_Id) combination.
Logic:
- - Identify duplicates grouped by Employee_Id and Warehouse_Id.
- - Keep the record with the MAX(Material_Handler_Warehouse_Id).
- - Delete all other duplicates.
*/
DELETE ud
FROM IN_Material_Handler_Warehouses AS ud
INNER JOIN (
SELECT
Employee_Id,
Warehouse_Id,
MAX(Material_Handler_Warehouse_Id) AS Keeper
FROM IN_Material_Handler_Warehouses
GROUP BY Employee_Id, Warehouse_Id
HAVING COUNT(*) > 1
) AS ref
ON ref.Employee_Id = ud.Employee_Id
AND ref.Warehouse_Id = ud.Warehouse_Id
AND ref.Keeper <> ud.Material_Handler_Warehouse_Id;
PRINT 'Duplicate Material Handler Warehouse records successfully removed.';
