...
These scripts retrieve holdings that should be reviewed. These scripts DO NOT update those holdings. These scripts just show valid/invalid Holdings from DB by conditions from comment.
Script 11a: Retrieve holdings where the source is anything other than FOLIO or MARC (e.g. -):
| Code Block |
|---|
| language | sql |
|---|
| title | Select Holdings where sourceName are not FOLIO and MARC |
|---|
|
SELECT
*
FROM ${tenant}_mod_inventory_storage.holdings_record
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' =NOT IN (
SELECT
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source) OR
${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' IS NULL; |
Script 1b: Update holdings where the source is anything other than FOLIO or MARC (e.g. -):
| Code Block |
|---|
| language | sql |
|---|
| title | Update Holdings where sourceId are not FOLIO and MARC or Null |
|---|
|
WITH source_id AS (
SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' != 'FOLIO'
)
ANDUPDATE ${tenant}_mod_inventory_storage.holdings_records_sourcerecord SET sourceid = (SELECT * FROM source_id)::uuid, jsonb = jsonb_set(jsonb, '{sourceId}', to_jsonb((SELECT * FROM source_id)), true)
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'name' != 'MARC'sourceId' NOT IN (SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source)
OR ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' IS NULL;; |
Script 2: Retrieve holdings where source = MARC and there is existing linked SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.
| Code Block |
|---|
| language | sql |
|---|
| title | Select Holdings where sourceName=MARC and external_hrid exists in SRS |
|---|
|
SELECT *
FROM ${tenant}_mod_inventory_storage.holdings_record RIGHT JOIN ${tenant}_mod_source_record_storage.records_lb
ON ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid' = ${tenant}_mod_source_record_storage.records_lb.external_hrid
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (
SELECT
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source
WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC'); |
Script 33a: Retrieve holdings where source = MARC and there is NO existing SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.
| Code Block |
|---|
| language | sql |
|---|
| title | Select Holdings where sourceName=MARC and external_hrid NOT exists in SRS |
|---|
|
SELECT *
FROM ${tenant}_mod_inventory_storage.holdings_record
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (
SELECT
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source
WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC')
AND NOT EXISTS
(SELECT *
FROM ${tenant}_mod_source_record_storage.records_lb
WHERE ${tenant}_mod_source_record_storage.records_lb.external_hrid = ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid'); |
Script 3b: Update holdings where source = MARC and there is NO existing SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.
| Code Block |
|---|
| language | sql |
|---|
| title | Update Holdings where sourceName=MARC and external_hrid NOT exists in SRS |
|---|
|
WITH source_id AS (
SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'FOLIO'
)
UPDATE ${tenant}_mod_inventory_storage.holdings_record SET sourceid = (SELECT * FROM source_id)::uuid, jsonb = jsonb_set(jsonb, '{sourceId}', to_jsonb((SELECT * FROM source_id)), true)
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC')
AND NOT EXISTS (SELECT * FROM ${tenant}_mod_source_record_storage.records_lb WHERE ${tenant}_mod_source_record_storage.records_lb.external_hrid = ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid'); |
...