Quirky update in sql server

 

When
you use variables in update statement and update the columns with the values of variables which keep on changing for each row, you can simulate many things that are done using Loop or a Cursor

 

declare @temp table
(
product_name varchar(100),
Value int,
sequence int
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 25 , 0)
insert into @temp values('product7' , 25 , 0)
insert into @temp values('product8' , 26 , 0)

declare @value int,@sequence int, @runningtotal int,@product_name varchar(100)

--Serial No for all values

set @sequence=0

update @temp
set @sequence=sequence =@sequence+1
select * from @temp

--Serial No for each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence else @sequence+1 end,@value=value

select * from @temp

--Reset Serial No in each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence+1 else 1 end,@value=value

select * from @temp

--Running total

set @sequence=0

update @temp
set @sequence=sequence =@sequence+value
select * from @temp

--Generate data in csv format

GO

 

declare @value int,@sequence varchar(100), @product_name varchar(100)

declare @temp table
(
product_name varchar(100),
Value int,
sequence varchar(100)
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 26 , 0)

 

set @product_name=''

update @temp
set sequence=@product_name ,
@product_name=case when @value=value then @product_name+','+product_name else product_name end,@value=value

select value,max(sequence) as sequence from @temp
group by value

The term "Quirky update" is referred in these articles
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

 

Published Wednesday, June 10, 2009 10:18 AM by Madhivanan

Comments

# Generate Fibonacci series - No loop, no recursion

Tuesday, September 1, 2009 2:46 PM by Madhivanan

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive

# Generate Fibonacci series - No loop, no recursion

Tuesday, September 1, 2009 3:01 PM by SQL Server Transact-SQL (SSQA.net)

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive