Example of performing batch updates while recording what objects were updated as a result of the batch. This is in addition to the Evergreen auditor schema, which records (by way of a trigger) the state of the objects before they are updated.

batch_update_example.sql

-- create a schema to record our batch changes
CREATE SCHEMA tadl_batch;

-- create a table for this set of changes
-- consider an approach with fewer tables
-- and use a batch_name column or similar
CREATE TABLE tadl_batch.acp_lost_in_migration (
id SERIAL PRIMARY KEY,
copy BIGINT NOT NULL,
batchtime TIMESTAMPTZ
);

-- insert copy IDs into our batch update table
-- leaving the batchtime blank, as they have
-- not actually been updated yet
INSERT INTO tadl_batch.acp_lost_in_migration (copy)
select acp.id
from action.open_circulation ocirc
join asset.copy acp on (ocirc.target_copy = acp.id)
join config.copy_status ccs ON (acp.status = ccs.id)
where ocirc.circ_lib in (select id from actor.org_unit_descendants(26))
and stop_fines = 'LOST'
and ccs.name = 'Checked out'
and acp.edit_date <= ocirc.xact_start;

-- a good place to begin a transaction
BEGIN;

-- Reference the UPDATE query as a CTE
-- and return the copy IDs which were updated.
-- Use this to update the batchdate in the
-- table within the tadl_batch schema
WITH updated AS (
  UPDATE asset.copy
  SET status = 3
  WHERE id IN (
    select acp.id from asset.copy acp
    join tadl_batch.acp_lost_in_migration tbalim ON (tbalim.copy = acp.id AND tbalim.batchtime IS NULL)
    join action.open_circulation ocirc ON (ocirc.target_copy = acp.id)
    WHERE acp.status = 1
    AND not acp.deleted
    AND ocirc.stop_fines = 'LOST'
    and acp.edit_date <= ocirc.xact_start
  ) returning id
)
UPDATE tadl_batch.acp_lost_in_migration
SET batchtime = now()
WHERE copy IN (
SELECT id
FROM updated
);

-- all of the rows in our batch table should have batchtimes now
select * from tadl_batch.acp_lost_in_migration;

-- Since this specific batch was updating the status value for each copy,
-- a trigger will have updated status_changed_time. 
-- now() is still the same value since we are within a transaction,
-- therefore this will show a count of the updated copies
select count(*) from asset.copy
WHERE status_changed_time = now();

-- if all's well, commit
COMMIT;