how to increment a int column using UPDATE

[gacaccia]

i have a sql server 2000 database with a table that includes an identity primary key field. it also includes an int field called "referenceCode" . the "referenceCode" value is set by the application that uses the database and is incremented for new records under certain conditions.

due to a design defect in the application, i've discovered that many of the "referenceCode" values are incorrect and need to be reincremented. for example, a subset of records in the table might have values of:

5010
5011
5012
5013

i would like to pick a new starting number and have that subset of records reincremented. for exmaple, start with 6000 to end up with:

6000
6001
6002
6003

any suggestions on how to accomplish that using an UPDATE statement?

thanks,

glenn

 

[r937]

UPDATE daTable
   SET ReferenceCode = ReferenceCode + 990
 WHERE ReferenceCode BETWEEN 5010 AND 5013

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gacaccia]

i was hoping for a more automatic method based on max referenceCode. the above requires me to calculate for each subgroup (which maybe is the fastest method in the end), but there are probably 30 to 40 subgroups that need to be reincremented. also, not all the subgroups are sequential like 1-2-3. some have jumps, so it might be 50-51-2023. on reincrement, i'd like to have it all sequential, so if the max referenceCode value is 2070, then on reincrement the above would become 2071-2072-2073.

 

[JoeAtWork]

I don't think anyone can offer an answer until you can tell us how to identify which records are to be changed.

Also I think it would only take a few minutes to update 30 or 40 groups with statements such as Rudy's.

 

[markros]

;with cte as (select IDField, ReferenceCode,
max(ReferenceCode) over (partition by GroupCode) + row_number() over (parition by GroupCode order by ReferenceCode) as NewRefCode from daTable where ...)

select * from cte -- to test

-- if OK, then

update cte set ReferenceCode = NewReferenceCode

Idea from the top of my head (for SQL 2005 and up only)

PluralSight Learning Library

 

[gacaccia]

unfortunately i'm stuck with sql server 2000. the problem with the original approach is that i run the risk of getting duplicates. i'm not sure it's worthwhile to get into a full explanation of why i need to do what i want to do and how i've gotten to that place. however, a simplified example....

TABLE TC
- field pk: primary key, auto increment
- field f1: varchar
- field f2: int

sample population:

1 - abc - 50
2 - abc - 51
3 - abc - 25634
4 - xyz - 55
5 - xyz - 56
6 - xzy - 25630
7 - mno - 50
8 - mno - 51
9 - mno - 25634

i want to reincrement field f2 based on the grouping of field f1. in the above example, i'd want to change the subgroup "mno" to something like 30000, 30001 and 30002. the values in the group "mno" need to be unique from the values in "abc".

 

[r937]

UPDATE tc
   SET f2 = pk + 29993 
 WHERE f1 = 'mno'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

How many groups do you have? If not many, I suggest

declare @Group varchar(20), @Id int, @Iterations int, @StartNum int

select identity (int, 1,1) as ID, f1 into #TempGroups
from TableToUpdate 
group by f1

set @Iterations = @@ROWCOUNT
set @ID = 1
while @ID <= @Iterations
  begin
    select @Group = f1 from #TempGroups where ID = @ID
    select @StartNum = max(f2) from TableToUpdate where f1 = @Group
    update TableToUpdate set @StartNum =f2 =  @StartNum + 1
 -- quirky update 
    where f1 = @Group
    set @ID = @ID + 1
  end

The above is again from the top of my head.


PluralSight Learning Library