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
BEGIN
SELECT RAISE(ABORT, 'Metadata values should never be updated');
END;
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 ('http://www.w3.org/2001/XMLSchema#string', '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 = 'http://purl.org/dc/elements/1.1/source';