...
| Code Block |
|---|
| language | sql |
|---|
| title | Update Holdings where sourceId are not FOLIO and MARC or Null |
|---|
|
BEGIN;
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'
NOT IN (SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source)
OR ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' IS NULL;
COMMIT; |
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 | Update Holdings where sourceName=MARC and external_hrid NOT exists in SRS |
|---|
|
BEGIN;
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');
COMMIT; |