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:
-- ***** 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