Occasionally I drop down to the database level to generate some reporting information. You could probably get the same information through the reporter but I like the precision of SQL. Here are a couple of queries that I've put together recently.
List titles for periodicals published by "Human Kinetics" with subscriptions owned by library ID "OSUL"
SELECT rsr.id, rsr.title FROM metabib.full_rec mfr INNER JOIN metabib.rec_descriptor mrd ON mfr.record = mrd.record INNER JOIN asset.call_number acn ON acn.record = mrd.record INNER JOIN reporter.super_simple_record rsr ON rsr.id = mrd.record INNER JOIN actor.org_unit aou ON aou.id = acn.owning_lib WHERE mfr.tag = '260' AND mfr.subfield = 'b' AND mfr.value ilike 'Human Kinetics%' AND mrd.bib_level = 's' AND aou.shortname = 'OSUL';
Strip out URLs for an online resource to which we no longer subscribe
Occasionally we drop subscriptions to an online resource that we happened to catalogue with an inline 856 field. Our new approach relies on just-in-time results from our link resolver to display accurate access to online resources (or at least consistent representations of what we have access to!), but our legacy records placed all of that information directly in the 856 field in the corresponding bibliographic record. The PostgreSQL regexp_replace() function lets you use regular expressions to match subsets of the MARC record and replace it with... well... nothing, in this case.
As we want to subsequently reingest the MARC records, and we're not running Evergreen trunk yet in which a reingest will automatically be triggered by an update to the biblio.record_entry table, I first push the list of affected IDs into a scratch table. This also lets me put limits on the MARC records that I'm going to touch, so that I don't inadvertently destroy content in another library's set of bibliographic records.
CREATE TABLE scratchpad.urls_to_delete (id BIGINT);INSERT INTO scratchpad.urls_to_delete SELECT acn.record FROM asset.uri au INNER JOIN asset.uri_call_number_map aucnm ON au.id = aucnm.uri INNER JOIN asset.call_number acn ON aucnm.call_number = acn.id INNER JOIN actor.org_unit aou ON acn.owning_lib = aou.id WHERE au.href ILIKE '%/search.ebscohost.com/direct.asp?db=rch%' AND aou.shortname = 'OSUL';BEGIN; UPDATE biblio.record_entry SET marc = regexp_replace( marc, E'<datafield tag="856" ind1="4" ind2="0"><subfield code="z">Available online from Ebsco.*?search.ebscohost.com/direct.asp\\?db=rch.*?</datafield>', '' ) WHERE id IN (SELECT id FROM scratchpad.urls_to_delete);
Note that the UPDATE statement is preceded by a BEGIN statement so that we can check our results and issue a ROLLBACK if we inadvertently changed too much, or created mangled records. Once you check your work with a SELECT statement or two, you can issue a COMMIT statement to make the changes take effect.