Thursday 25 October 2012

Update SQL Table column with number range


There might be scenario you might want to update the column with number range. Here is the simple solution for doing so. 

For example from this (Figure 1) to this (Figure 2)


Figure 1
 
Figure 2

Below is the SQL code for above result.


--//PURPOSE: This sample is to demonstrate the trick 
--// of updating the SQL table column with sequence numbering.
--//Decalre the table variable
declare @TempTable as table(
[ID] INT,
[Name] varchar(50),
[Range] INT
)
--//Insert rows in the Table variable
insert into @TempTable values(1, 'Mumbai', null)
insert into @TempTable values(2, 'Navi Mumbai', null)
insert into @TempTable values(3, 'Delhi', null)
insert into @TempTable values(4, 'Kolkata', null)
insert into @TempTable values(5, 'Chennai', null)
insert into @TempTable values(6, 'Assam', null)

--//Do select to check the result of table variable with range column equals to null.
select * from @TempTable

--//Decalre the variable
declare @cnt int 
--//Set the variable to the value you want to keep as start.
set @cnt = 100

--//HERE IS TRICK. The single update statement to update entire table
update @TempTable set @cnt = [Range] = (@cnt + 1)

--//Check the result of the updated column [Range]

select * from @TempTable


Happy SQL Scripting !!!!




No comments:

Post a Comment