Difference between revisions of "SUNScholar/SQL Tips"
Jump to navigation
Jump to search
m |
m |
||
| Line 17: | Line 17: | ||
;Empty type | ;Empty type | ||
*SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type') AND text_value = ''; | *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%'; | ||
| + | |||
| + | ;Update/replace metadata | ||
| + | *UPDATE metadatavalue SET text_value = 'Journal Article' WHERE text_value = 'Journal Articles' | ||
Revision as of 15:46, 22 September 2014
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%';
- Update/replace metadata
- UPDATE metadatavalue SET text_value = 'Journal Article' WHERE text_value = 'Journal Articles'
