Bulk Update Patron Addresses Based on Given Address

Clearinghouse Repository Polaris Repository
,
1

This query will bulk update multiple patron addresses based upon a “master address” and will apply to all patrons within a given patron record set. There are a few steps to this process:

  1. For all the patrons you want to update, put them into a record set and use its RecordSetID in the query. (Line 31) Also, note down the number of records within that record set.

  2. Create or alter a single patron record that remains outside of your patron record set. Give this patron the address you want all of the other patrons in the record set to have. Once you’ve done that, make a note of that patron’s PatronID. For this example, we’ll say that PatronID is 161803.

  3. In ADS or SSMS, run the following query, replacing the PatronID of 161803 with the PatronID of the patron you set up in Step 2.

SELECT AddressID FROM Polaris.Polaris.PatronAddresses WITH (NOLOCK)
WHERE PatronID = 161803

  1. Note down that AddressID number and use it in the query below. (Line 34)

  2. Make the changes needed below. For your saftey, use the BEGIN TRAN along with the COMMIT and ROLLBACK commands. When you run your initial query with BEGIN TRAN, make sure that the number of rows affected matches the number of records in your patron record set. (Step 1)

  3. Run the query and COMMIT or ROLLBACK as needed.

/* First, let's set up some variables */

-- Replace the number below with the RecordSetID of your patron record set
DECLARE @MyPatronRecordset INT = 367545;

-- Replace the number below with the AddressID you obtained in Step 3
DECLARE @MasterAddressID INT = 4089621;

-- Use BEGIN TRAN to make sure we don't hose a bunch of patron data
BEGIN TRAN

UPDATE
    Polaris.Polaris.PatronAddresses
SET
    AddressID = @MasterAddressID
WHERE
    AddressTypeID = 2
AND
    PatronID IN (
        SELECT PatronID
        FROM Polaris.Polaris.PatronRecordSets WITH (NOLOCK)
        WHERE RecordSetID = @MyPatronRecordset
    );


-- Like what you see? Then uncomment and execute the line below and COMMIT the changes.
-- COMMIT;

-- Something wrong? Uncomment the line below and execute the line below to ROLLBACK your changes.
-- ROLLBACK;