Quote from kevync on 2025-10-16, 11:41 am

Recently I reorganized about 18,000 media from one folder to the subfolders of the parent media folder.  I then used the built in media tool to fix broken media link -- for some reason -- it ended up create duplicate media items   for many of the same Filename. I had one before the tools was run and ended up with nearly 500 after.  This fix is relatively simple  -- I used AI for assistance -- the first script fixed all but 20 which was good enough for me . Took me about 5 mins to fix those manually.   There is a thread on RM and I opened   a ticket.
https://community.rootsmagic.com/t/odd-issue-when-using-find-missing-media-tool/14910

here are some scripts that might help:

//cte ver I did not test

-- Step 1: Define a CTE to find all filenames that appear more than once.
WITH DuplicateFilenames AS (
SELECT MediaFile
FROM MultimediaTable
GROUP BY MediaFile
HAVING COUNT(MediaID) > 1
),

-- Step 2: Define a CTE to find all MediaIDs that have exactly one link (LinkID).
SingleLinkedMedia AS (
SELECT MediaID
FROM MediaLinkTable
GROUP BY MediaID
HAVING COUNT(LinkID) = 1
)

-- Final DELETE statement
-- Delete records from MultimediaTable where:
-- 1. The MediaFile is a recognized duplicate (from CTE 1).
-- 2. The MediaID is only linked once (from CTE 2).
DELETE FROM MultimediaTable
WHERE MediaFile IN (SELECT MediaFile FROM DuplicateFilenames)
AND MediaID IN (SELECT MediaID FROM SingleLinkedMedia);

version tested

 //non cte ver TESTED

DELETE FROM MultimediaTable
WHERE MediaID IN (
-- Step 2: Identify the MediaID(s) from the duplicate MediaFiles
SELECT T1.MediaID
FROM MultimediaTable AS T1

-- Step 3: Check the link count for the identified MediaID(s)
INNER JOIN (
SELECT MediaID, COUNT(LinkID) AS LinkCount
FROM MediaLinkTable
GROUP BY MediaID
HAVING LinkCount = 1
) AS T2
ON T1.MediaID = T2.MediaID

-- Step 1: Identify MediaFiles that are duplicates
WHERE T1.MediaFile IN (
SELECT MediaFile
FROM MultimediaTable
GROUP BY MediaFile
HAVING COUNT(MediaID) > 1
)
);

also to know if you have duplicates

//find if what duplicates media

SELECT Count(MediaID), Group_ConCat(MediaID), MediaFile, MediaPath FROM "MultimediaTable"
Group By MediaFile, MediaPath
Having Count(MediaID) > 1
ORDER BY MediaFile, MediaPath