Q:

SQL : Iterate through rows and update set of rows that have same primary key

I have a table called *Provider_Compliances*
 (ProviderNum,
 ProviderLocation,
 HistorySequence,
 ProviderEffectiveDate,
 PeriodBeginDate,
 PeriodEndDate,
 IsCompliant)
Example of data :
ProviderNum |ProviderLocatin  |HistorySequence|ProviderEffectiveDate|PeriodBeginDate|PeriodEndDate|IsCompliant
         1  |              1  |          2  |   2012-01-01        |  2010-01-03   | 2012-01-01  | No
         2  |              2  |          2  |   2012-01-01        |  2012-01-02   | 9999-12-31  | Yes

The primary key for the Provider_Compliances Table is (ProviderNumber,HistorySequence, ProviderLocation, ProviderEffectiveDate).
I am trying to add a new columns (ComplianceNumber) and then change the primary key of the Provider_Compliances Table to composite key (ComplianceNumber, HistorySequence).
I don't want to lose any preexisting data.
So, I thought of doing it this way :
//1st, add the new column(ComplianceNumber) with default value 0
ALTER TABLE Provider_Compliances ADD ComplianceNumber DECIMAL(10) DEFAULT 0 

//2nd, update all existing rows 
UPDATE Provider_Compliances SET ComplianceNumber = (NEXT VALUE FOR Provider_Compliances_Sequence)

//3rd, modify the primary key
ALTER TABLE Provider_Compliances DROP PRIMARY KEY
ALTER TABLE Provider_Compliances ADD PRIMARY KEY(ComplianceNumber, HistorySequence)

The problem with step 2 is that it will set every row to a new ComplianceNumber.
I want every row that has the same ProviderNumber + ProviderLocation + ProviderEffectiveDate to be granted the same ComplianceNumber.

A:

You will be able to do this with a SQL script as follows

Create a temp table with records group by ProviderNumber +
ProviderLocation + ProviderEffectiveDate including new column
Loop through temp table and update the new column (with incremental number - or as you needed)
Join this table with original one & do the update new column of original table with the value in temp table

