Sample UNTESTED code for updating Polaris Patron Address Types

If you’d like to remove some in use Patron Address Types, this UNTESTED script might be able to help. You should test this thoroughly on a non-production system before running!

*Note: Sadly, these are called AddressTypeIDs in one table and AddressLabelIDs on another table :frowning:

The IDs you’ll need for this script come from the following table:

-- ***** IMPORTANT SAFEGUARDS *****
-- 1. ALWAYS BACK UP your database or at least the [PatronAddresses] table before running mass updates.
-- 2. TEST this script thoroughly in a non-production (test/development) environment first.
-- 3. REVIEW the row count output carefully after execution.
-- *********************************

-- == Configuration ==
-- Replace NULL with the actual numeric ID of the Address Label you want to change FROM
DECLARE @OldAddressLabelID INT = NULL;
-- Replace NULL with the actual numeric ID of the Address Label you want to change TO
DECLARE @NewAddressLabelID INT = NULL;
-- == End Configuration ==

-- Input Validation: Check if variables are set
IF @OldAddressLabelID IS NULL OR @NewAddressLabelID IS NULL
BEGIN
    PRINT 'Error: Please specify the numeric values for @OldAddressLabelID and @NewAddressLabelID variables.';
    -- Using RAISERROR will stop execution in most SQL clients
    RAISERROR('Script configuration incomplete. Variables @OldAddressLabelID and @NewAddressLabelID must be set.', 16, 1);
    RETURN; -- Ensures script stops if variables aren't set
END

-- Safety Check: Verify the new AddressLabelID exists in the reference table
IF NOT EXISTS (SELECT 1 FROM [Polaris].[Polaris].[AddressTypes] WHERE AddressTypeID = @NewAddressLabelID)
BEGIN
    -- If the new ID is not found, print an error and stop.
    PRINT 'Error: The target AddressLabelID (' + CAST(@NewAddressLabelID AS VARCHAR(10)) + ') does not exist in the reference table [Polaris].[Polaris].[AddressTypes]. Please verify the table name and ID.';
    PRINT 'Update aborted. No changes were made.';
    RETURN; -- Stop execution if the new ID is invalid
END
ELSE
BEGIN
    -- If the new ID is valid in the assumed reference table, print a confirmation message.
    PRINT 'Validation successful: The target AddressLabelID (' + CAST(@NewAddressLabelID AS VARCHAR(10)) + ') exists in the reference table [Polaris].[Polaris].[AddressTypes].';
END

-- Optional: Preview rows that *would* be updated (uncomment the block below to run this check first)
/*
PRINT '--- Previewing Rows to be Updated ---';
SELECT COUNT(*) AS RowsToUpdate
FROM [Polaris].[Polaris].[PatronAddresses]
WHERE [AddressLabelID] = @OldAddressLabelID;

SELECT TOP 100 -- Limit preview for performance
      [PatronID]
     ,[AddressID]
     ,[AddressTypeID]
     ,[Verified]
     ,[VerificationDate]
     ,[PolarisUserID]
     ,[AddressLabelID] -- This is the value that will change
 FROM [Polaris].[Polaris].[PatronAddresses]
 WHERE [AddressLabelID] = @OldAddressLabelID;
PRINT '--- End Preview ---';
-- You might want to stop here, review the preview, and then run the update block below separately.
-- RETURN;
*/

-- Perform the update within a transaction
-- BEGIN TRANSACTION ensures that the entire update operation is treated as a single unit.
-- If any part fails, the whole operation can be undone (rolled back).
BEGIN TRANSACTION;

BEGIN TRY
    -- The actual UPDATE statement
    -- It changes the AddressLabelID to the new value (@NewAddressLabelID)
    -- only for rows where the current AddressLabelID matches the old value (@OldAddressLabelID).
    UPDATE [Polaris].[Polaris].[PatronAddresses]
    SET [AddressLabelID] = @NewAddressLabelID -- Set the new value
    WHERE [AddressLabelID] = @OldAddressLabelID; -- Only update rows with the old value

    -- If the update command succeeds without errors, commit the changes permanently to the database.
    COMMIT TRANSACTION;

    PRINT 'Update operation completed successfully.';
END TRY
BEGIN CATCH
    -- If any error occurs during the UPDATE statement execution inside the TRY block...
    PRINT 'An error occurred during the update process.';

    -- Check if there's an active transaction resulting from the BEGIN TRANSACTION above.
    IF @@TRANCOUNT > 0
    BEGIN
        -- If there is an active transaction, undo all changes made since BEGIN TRANSACTION.
        ROLLBACK TRANSACTION;
        PRINT 'Transaction rolled back. No changes were saved to the database.';
    END

    -- Display details about the error that occurred.
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(20));
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(20));
    PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(20));
    PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(20));
    PRINT 'Error Message: ' + ERROR_MESSAGE();

END CATCH;
GO -- Standard batch separator in SQL Server Management Studio and sqlcmd