Batch file name modifications using SQL with a Lightroom catalog

I somehow managed to f-up my Lightroom image files import from my camera, and ended up with ~70 duplicates, with names ending in -2.NEF.

Files appear twice in the Windows file explorer, but I only have files suffixed by -2 in Lightroom.

Of course I only noticed that after I had spent hours editing the duplicates!

I began to remove the files manually and tried to synchronize files through Lightroom, but I quickly understood I would lose all my edits as the tool didn't seem able to reconciliate the new names with the old ones.

Not acceptable.

I was about to fix the imports painfully and manually one by one, then I remembered Lightroom catalogs are just glorified SQLite databases, and started the wonderful DB Browser for SQLite.

Would it be possible to update the file names, removing the -2 suffixes without breaking anything?

How hard can it be?

Not that hard it turns out!

After making a backup, I took a look at the multiple SQL tables in the catalog using the DB Browser.

I only saw file names in a single table, named AgLibraryFile.

The file names appear in multiple columns of the table though, but it seemed simple enough to update them.

Rows appear to be contiguous and sorted by insertion date.

After finding all the rows with suffixed files, I copy pasted their ids and started to craft my SQL query:

select
  REPLACE(baseName,'-2','')
, REPLACE(idx_filename,'-2','')
, REPLACE(lc_idx_filename,'-2','')
, REPLACE(originalFilename,'-2','')
, * from AgLibraryFile
where id_local in (
3815620,
3815647,
3815795,
3815822,
...
3815843
)

Sweet!

I double checked everything looked correct, then I changed my select to an update, fixing all the rows in one go:

update AgLibraryFile
set
  baseName=REPLACE(baseName,'-2','')
, idx_filename=REPLACE(idx_filename,'-2','')
, lc_idx_filename=REPLACE(lc_idx_filename,'-2','')
, originalFilename=REPLACE(originalFilename,'-2','')
where id_local in (
3815620,
3815647,
3815795,
3815822,
...
3815843
)

Execution finished without errors.
Result: query executed successfully. Took 13ms

Done!