How to populate a newly added column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to populate a newly added column?

Author

Topic

melchor010
Starting Member

2 Posts

Posted - 2011-02-18 : 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
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-19 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources