# Sample UNTESTED code for updating Polaris Patron Address Types

**URL:** https://forum.innovativeusers.org/t/sample-untested-code-for-updating-polaris-patron-address-types/2425
**Category:** Polaris Repository
**Tags:** sql
**Created:** [April 9, 2025, 3:30pm UTC](https://forum.innovativeusers.org/t/sample-untested-code-for-updating-polaris-patron-address-types/2425 "2025-04-09T15:30:15Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![wesochuck](https://forum.innovativeusers.org/user_avatar/forum.innovativeusers.org/wesochuck/32/499_2.png) [@wesochuck](https://forum.innovativeusers.org/u/wesochuck)
#### Post date: [April 9, 2025, 3:30pm UTC](https://forum.innovativeusers.org/t/sample-untested-code-for-updating-polaris-patron-address-types/2425/1 "2025-04-09T15:30:15Z")

</div>

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 ☹

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

 ![image](https://forum.innovativeusers.org/uploads/db7787/original/2X/0/06de3950dfb83f8706e1e46f4684fb76a9f82d3d.png)

```auto
-- *****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

```