SUNScholar/SQL Tips

Back to System Admin

Below are SQL statements to be used for various purposes.

Extract metadata

• SELECT * FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'issused' )
• SELECT * FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'contributor' AND qualifier = 'advisor' )

Find short dates

• SELECT * FROM metadatavalue,metadatafieldregistry WHERE metadatavalue.metadata_field_id = metadatafieldregistry.metadata_field_id AND element = 'date' AND qualifier = 'accessioned' AND CHAR_LENGTH(text_value) < 10;

Empty date

• SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned' ) AND text_value = ;

Empty type

• SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type') AND text_value = ;

Search for metadata

• SELECT * FROM metadatavalue WHERE text_value like '%Peer%';
• SELECT metadatavalue.text_value FROM public.metadatavalue WHERE metadatavalue.text_value = 'JournalArticles';

Update/replace metadata

• UPDATE metadatavalue SET text_value = 'Journal Article' WHERE text_value = 'Journal Articles'
• UPDATE metadatavalue SET text_value = 'Article' WHERE text_value = (SELECT metadatavalue.text_value FROM public.metadatavalue WHERE metadatavalue.text_value = JournalArticles'); UPDATE +1

List items in collections

• SELECT item_id FROM collection2item WHERE collection_id= '1297';

List items in collections by title

• SELECT metadatavalue.item_id, metadatavalue.text_value FROM public.collection2item, public.metadatavalue WHERE collection2item.item_id = metadatavalue.item_id AND collection_id = 1289 AND metadata_field_id = 64 ORDER BY metadatavalue.item_id ASC;

Update/set template

• UPDATE collection SET template_item_id = AAA FROM community2collection WHERE collection.collection_id = community2collection.collection_id AND community2collection.community_id = BBB;

IR Growth

SELECT * FROM ((SELECT 'dc.date.accessioned' AS field, substring(text_value FROM 1 FOR 4) AS year,substring(text_value FROM 6 FOR 2) AS month, COUNT(item_id) as numberOfDocs FROM metadatavalue WHERE metadata_field_id = (SELECT metadata_field_id FROM metadatafieldregistry WHERE metadata_schema_id = 1 AND element = 'date' AND qualifier = 'accessioned') GROUP BY year, month ORDER BY year,month) UNION (SELECT 'dc.date.available' AS field, substring(text_value FROM 1 FOR 4) AS year,substring(text_value FROM 6 FOR 2) AS month, COUNT(item_id) as numberOfDocs FROM metadatavalue WHERE metadata_field_id = (SELECT metadata_field_id FROM metadatafieldregistry WHERE metadata_schema_id = 1 AND element = 'date' AND qualifier = 'available') GROUP BY year, month ORDER BY year,month)) AS accessAvail ORDER BY field, year, month;