Help with Update statement - SQL Server Forums

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

Help with Update statement

Author

Topic

ddt
Starting Member

10 Posts

Posted - 2010-06-08 : 23:17:25

Hi
I have two tables and i need to update data in one column based on another column
Source Data table
ClassID CourseNum StudentId Interval Period Grade
------- --------- --------- -------- ------ -----
1 CRS001 100100 2 0 A+
1 CRS001 100100 1 1 B+
1 CRS001 100100 2 1 C+

Destination Data
ClassID CourseNum StudentId Q1 Q2 Q3
------- --------- --------- -- -- --
1 CRS001 100100 A+ B+

I know i have to filter Source table on Iterval and period to get grade for reporting period
and i need to mathc source StudentID+CourseNum+ClassID and than write to Q3 Column...

Appreciate any help and hints...

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-09 : 00:35:57

Q3 is corresponding to which Interval and Period ?

KH
[spoiler]Time is always against us[/spoiler]

ddt
Starting Member

10 Posts

Posted - 2010-06-09 : 01:30:36

Interval 2, Period 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-09 : 02:20:18

[code]
update d
set Q3 = s.Grade
from source s
inner join destination d on s.ClassID = d.ClassID
and s.CourseNum = d.CourseNum
and s.StudentId = d.StudentId
where s.Interval = 2
and s.Period = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ddt
Starting Member

10 Posts

Posted - 2010-06-09 : 05:10:24

thanks...
while i was waiting for response i created some views and merged data source and destination in one table and than i used update. your solution is easier though..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources