Deleting Duplicate Objects in M-Files

This example shows how to mark duplicates for deletion by setting Process_ID, then use spMFDeleteObjectList to remove them.

Identify and Delete Duplicate Records

UPDATE mic2
SET mic2.Process_ID = 1
FROM dbo.MFInventoryCons AS mic2
    INNER JOIN (
        SELECT list2.ObjID
        FROM (
            SELECT mic.Cast,
                   mic.Mill_Plate,
                   mic.ObjID,
                   ROW_NUMBER() OVER (PARTITION BY mic.Cast, mic.Mill_Plate ORDER BY mic.Cast, mic.Mill_Plate) AS Rownr
            FROM dbo.MFInventoryCons AS mic
                INNER JOIN (
                    SELECT mic.Cast, mic.Mill_Plate
                    FROM dbo.MFInventoryCons AS mic
                    GROUP BY mic.Cast, mic.Mill_Plate
                    HAVING COUNT(*) > 1
                ) l ON l.Cast = mic.Cast AND l.Mill_Plate = mic.Mill_Plate
        ) list2
        WHERE list2.Rownr > 1
    ) list3 ON mic2.ObjID = list3.ObjID;

Delete Marked Duplicates

EXEC dbo.spMFDeleteObjectList @TableName = N'MFInventoryCons',
                              @Process_id = 1,
                              @Debug = 0,
                              @DeleteWithDestroy = 0;