Explanation of SQL
This SQL will correct a problem in the InventoryLog table where the InventoryID it points to is a warehouse or division summary and not the actual InventoryID.
Risk of Data Corruption if Run Improperly
High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.
SQL
-- query to make sure the InventoryID of the InventoryLog points to the correct -- Inventory item. Due to something (an old bug?), it appears to point to an old warehouse record -- for that inventory item -- get the correct inventoryID from the part UPDATE InventoryLog SET InventoryClassTypeID = 12200, InventoryID = ( SELECT ID AS InventoryID --ClassTypeID as InventoryClassTypeID, --coalesce(DivisionID, 10) as DivisionID, --coalesce(WarehouseID, 10) as WarehouseID FROM Inventory I WHERE ClassTypeID = 12200 AND I.PartID = InventoryLog.PartID AND COALESCE(I.DivisionID, 10) = COALESCE(InventoryLog.FromDivisionID, 10) AND COALESCE(I.WarehouseID, 10) = COALESCE(InventoryLog.FromWarehouseID, 10) ) WHERE InventoryID ( SELECT ID AS InventoryID --ClassTypeID as InventoryClassTypeID, --coalesce(DivisionID, 10) as DivisionID, --coalesce(WarehouseID, 10) as WarehouseID FROM Inventory I WHERE ClassTypeID = 12200 AND I.PartID = InventoryLog.PartID AND COALESCE(I.DivisionID, 10) = COALESCE(InventoryLog.FromDivisionID, 10) AND COALESCE(I.WarehouseID, 10) = COALESCE(InventoryLog.FromWarehouseID, 10) )
Version Information
- Entered : 2/2011
- Version : 4.5+
You could leave a comment if you were logged in.