Does anyone have an SQL for patron address bulk change? I have a school with several hundred students that is wanting to the change to patron address to reflect the physical address of the school.
1 Like
Are you changing the full address (such as the Notify address) and, if so, is it the full address being changed? In other words you’d be updating the street address, city, state, and postal code?
Or are you just updating part of it?
Hi! Just part of it, the street address shown. The city, state and zip will remain the same.
1 Like
SHWEET.
That makes it easier! 
Give me a few minutes and I’ll get back to ya!
Okay, so you’ll want to do a few things to get this set up. I’ve detailed those in the query itself, but here’s a quick rundown in plain English:
First, either set up or modify a patron record that has the address you want to use. Say you’ve got a student named Cayce Pollard, modify her registration so she has the address you want to use. Note down her PatronID.
Second, run the following query, replacing the PatronID with Cayce’s:
SELECT AddressID FROM Polaris.Polaris.PatronAddresses WITH (NOLOCK)
WHERE PatronID = 161803 -- Replace this PatronID with Cayce's PatronID
That will return an AddressID and that AddressID has all the information you’ll need to update your other patrons.
Now third, make a patron record set with all the patrons you want to update but, when you do this, leave Cayce’s account out of it. Note down the RecordSetID of your patron record set and how many records are in it.
Fourth, fill in the query below with the AddressID and the RecordSetID. I’ve put a BEGIN TRAN command at the beginning with commented COMMIT and ROLLBACK at the end. When you run this query, it’ll tell you how many rows will be affected. That number should match the number of records in your patron record set. If it doesn’t, something is wrong.
And finally, if the number looks right, COMMIT the changes. If it doesn’t look right, ROLLBACK and see what might have gone sideways.
Okay, all that done, here’s the query. If you have a training server, I’d suggest running it there first, just to make sure. But it worked as expected on my development server.
/* 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
4. Note down that AddressID number and use it in the query below. (Line 34)
5. 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)
6. Run the query and COMMIT or ROLLBACK as needed. */
/* ----- BEGIN UPDATE QUERY ---------------------- */
/* 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;
Let me know if you have any questions!
Wow! Thank you so much. I am sick right now with a head cold from hell, as soon as I get back to the living, I will give this a try. I will admit that I don’t have much experience with SQL, but I’ll try anything once!
1 Like
OH NO!
Get some rest and fluids and lots of bad Netflix. The bad Netflix is very important for curing a cold! Feel better soon!