Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

updating a column Expand / Collapse
Author
Message
Posted Sunday, December 19, 2010 11:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
create table #test_table(sno int identity(1,1),line_no int null,product_name varchar(20))
insert into #test_table(product_name) values('TV')
insert into #test_table(product_name) values('Mobile')
insert into #test_table(product_name) values('Webcam')
insert into #test_table(product_name) values('Keyboard')

Now i need line_no column incremented by 1 starting from 1.... using update command in ascending order of sno column.....


any help pls?
Post #1037023
Posted Monday, December 20, 2010 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
The Row_Number function will do what you want. First write a select that returns the data you what, then use that in the from clause of the update.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1037053
Posted Monday, December 20, 2010 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
thanks a lot gail....
Post #1037121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse