Changing Metadata en Masse

I’ve just successfully changed a few thousand paths using the instructions here. Thank you!
Is it possible to change metadata in a similar way? There are two types of things I’d like to do:

  1. In the “dc:source” field, say I’d recorded the citation “Personal Archives, Paris, France.” Those archives have now moved and I need to change 500 items to say “Personal Archives, London, UK” instead.
  2. Say I have a series of photos named “01.png,” “02.png,” etc. But I need to change these to “0001.png,” “0002.png,” etc. I can change the file names easily with the Bulk Rename Utility, and I can even change the paths in Tropy now, but can I also update the photos’ titles as they appear in Tropy’s photo panel?

I used this for #1:
update metadata_values set text=replace(text,'Personal Archives, Paris, France','Personal Archives, London, UK');

But I received this result:
Execution finished with errors. Result: Metadata should never be updated

Hm, I feel like we’re only missing the advanced search functionality to do this entirely via the UI. You’d have to be able to search for exactly those items that have a specific value for dc:source and then you could change the value in the bulk editor. If there’s a reliable way to select all the pertinent items you could probably just do this; even if it requires 2 or 3 ‘batches’ it would still be fairly quick. For example you could put “Personal Archives, Paris, France” in the quick search bar (including the quotation marks); unless this matches unrelated items as well (because that string also occurs in notes or other values), you can select all the items and just change the value in the bulk editor to all of them. (Currently this only works for values at the item-level.)

Otherwise, to get this done right now it’s probably best to go to the database directly. The reason your attempt #1 produces the error is because the metadata values are shared. If you have 100 items with a value “Personal Archives, Paris, France” (regardless of the property id) this value is only stored once in the database (and associated with 100 metadata fields).If you change this value you’re potentially changing it in a lot of places, that’s why we have a constraint that prohibits this. For this specific string changing it everywhere is probably correct though, so one solution could be to disable the constraint temporarily. Otherwise, the way to update metadata fields is to attach a new value instead of the old one.

As the usual disclaimer, please always make a backup copy if you make changes to the database file!

So, the first option would be:

drop trigger update_metadata_values_abort;
update metadata_values set text = 'Personal Archives, London, UK' where text = 'Personal Archives, Paris, France';

This should update the value. Now you should re-create the trigger like this:

CREATE TRIGGER update_metadata_values_abort
  BEFORE UPDATE ON metadata_values
    SELECT RAISE(ABORT, 'Metadata values should never be updated');

This should work but be careful with this, because it can have unintended consequences for less specific strings which could be used by different metadata fields.

The safer way would be to determine which items have this exact value as dc:source and change only those values. This would go something like this.

First, create the string value (if it does not exist yet):

insert into metadata_values (datatype, text) values ('', 'Personal Archives, London, UK');

Next, you need to note the value id of the string you just created and also the value id of the original string you want to replace for example:

select value_id, text from metadata_values where text like 'Personal Archives,%';

Should probably be good enough to show you these two value ids.

Now you can replace the values in all metadata fields where they are used as dc:source. For example, if the id of the original string is 9 and you want to change it to 24:

  update metadata set value_id = 24 where value_id = 9 and property = '';

Changing the number format is probably more tricky. With some caution, you could probably achieve this by updating the metadata values directly (dropping the trigger temporarily as explained earlier) and using the replace function, but I can’t immediately come up with a simple query that would do this safely.

This would be a good fit for a generic ‘Find and Replace’ functionality with support for pattern substitution built into Tropy’s UI.

Thank you very much. This all worked perfectly.

You’re right, of course. I couldn’t do that in my case because of something I hadn’t mentioned in my original post: each of my items had a unique citation, like this: “File 2, Box 4, Personal Archives, Paris, France,” “File 3, Box 4, Personal Archives, Paris, France,” “Box 63, Personal Archives, Paris, France,” etc.