Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Script - Update Blank Values of Group until Next group starts

Total Hit ( 2185)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


Recently I came across simple scenario where I had to update some blank values of certain records. Here is what i mean

Assume you have two columns. You want to update Order# column with same order number until next group starts (e.g. ID 2,3 update with Order# => 1000).

ID Order#
1  1000
2  NULL
3  NULL
4  2000
5  NULL
6  NULL
7  3000
8  NULL
9  NULL

Click here to copy the following block
set nocount on
go

create table #tmp (ID int, OrderNum varchar(10))
insert into #tmp values(1,1000)
insert into #tmp values(2,NULL)
insert into #tmp values(3,NULL)
insert into #tmp values(4,2000)
insert into #tmp values(5,NULL)
insert into #tmp values(6,NULL)
insert into #tmp values(7,3000)
insert into #tmp values(8,NULL)
insert into #tmp values(9,NULL)


declare @id int
declare @ordernum varchar(100)
declare @prev_ordernum varchar(100)

select * from #tmp

declare c cursor
for
select id,OrderNum from #tmp

open c

fetch next from c into @id,@ordernum

set @prev_ordernum=@ordernum

while @@fetch_status<>-1
begin
   --Update records with blank order#
   if IsNull(@ordernum,'')='' and isnull(@prev_ordernum,'')<>''
   begin
       Update #tmp set ordernum=@prev_ordernum where ID=@id
       Print 'updated --> ' + cast(@id as varchar(100))
   end

   fetch next from c into @id,@ordernum
   
   --//New order num group found
   if isnull(@ordernum,'')<>'' and @prev_ordernum<>@ordernum
   set @prev_ordernum=@ordernum
end
close c
deallocate c

select * from #tmp

go

go
drop table #tmp

Output

Click here to copy the following block
ID    OrderNum
1    1000
2    1000
3    1000
4    2000
5    2000
6    2000
7    3000
8    3000
9    3000


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.