Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Quirky update in sql server

Jun 10 2009 10:18AM by Madhivanan   

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/

Tags: t-sql, sql_server, quirky,


Madhivanan
3 · 40% · 12862
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • This is really great! Thank-you, Madhivanan!

    commented on Dec 27 2010 1:13AM
    Nakul Vachhrajani
    4 · 36% · 11533
  • Although it will likely always work correctly on such a small sample of data, some critical "rules" have been omitted. Please DO read the article at the link Madhivanan posted and make sure you follow all of the "rules" for using this method. Here's that link again for your convenience:

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

    commented on Feb 15 2011 11:32PM
    Jeff Moden
    161 · 1% · 300
  • Thanks Jeff for the feedback

    commented on Feb 16 2011 2:13AM
    Madhivanan
    3 · 40% · 12862

Your Comment


Sign Up or Login to post a comment.

"Quirky update in sql server " rated 5 out of 5 by 2 readers
Quirky update in sql server , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]