/* NOTE, you'll need to run this code in two stages: in SQL Developer, first run the abovequery querybelow, then cut/paste the results back into a new query, and run that query */
SELECT 'UPDATE metadatavalue SET text_value = ''http://hdl.handle.net/'||handle||''' WHERE metadatavalue.metadata_field_id = '||metadata_field_id
FROM metadatavalue
JOIN metadatafieldregistry USING(metadata_field_id)
JOIN handle ON handle.resource_id = metadatavalue.item_id
WHERE metadatafieldregistry.element = 'identifier'
AND metadatafieldregistry.qualifier = 'uri'
AND handle.resource_type_id = 2
AND to_char(text_value) = 'http://hdl.handle.net/XXXXX'
;
|