|
melchor010
Starting Member
2 Posts |
 Posted - 02/18/2011 : 18:19:11
|
I have a table with the following fields:
StudentNo, Class, Grade. I added a new column called sequence. I have close to 5000 records to update. How do I populate the column so that the table will look like below?
Student Class Sequence Grade
1 A 1 80
1 B 2 75
1 C 3 82
2 D 1 72
2 E 2 86
Thank you so much.
Melchor |
|
|
pk_bohra
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 02/19/2011 : 00:17:39
|
You have not mentioned what version of SQL you are using. If you are using 2005 or higher then the below logic should work for you.
--Sample table structure.
Create table #Students
(
StudentNo int,
Class varchar(50),
Grade int
)
Insert into #Students
Select 1,'A', 80 union all
Select 1 ,'B', 75 union all
Select 1 ,'C',82 union all
Select 2 ,'D',72 union all
Select 2 ,'E', 86
Alter table #Students
Add Seq int
--Actual Logic for updation starts here
;with cte
as
(
Select Row_number() Over (Partition by StudentNo order by StudentNo) as Srno,
* from #Students
)
update cte set seq = srno
select * from #Students
|
 |
|
How to populate a newly added column?
