I have a table tblStudentMarks which has StudentId, SubjectID and Student Marks. In the following table there are two student IDs (1and 2). Write a single update query using join that updates the marks of second student (StuID = 2) with the same marks of First student (StuID = 1) on each subjectID.

SlNo StuID SubID StuMark
1 1 1 75
2 1 2 88
3 1 3 96
4 1 4 84
5 1 5 80
6 2 1 NULL
7 2 2 NULL
8 2 3 NULL
9 2 4 NULL
10 2 5 NULL

 Posted by Nagasundar_Tn on 11/27/2012 | Category: Sql Server Interview questions | Views: 1701 | Points: 40
Answer:

The code is

  update b set b.StuMark = a.StuMark from tblSTUDENTMARKS a

inner join tblSTUDENTMARKS b on a.SubID = b.SubID
where a.StuId = 1 and b.StuID = 2

Here I am using join based on SubjectID not with SlNo. Because SubjectID only same for both students. I am using alias name 'a' and 'b' for tblStudentMarks table.

If I replace the update statement of above query with Select statement,

  select * from tblSTUDENTMARKS a

inner join tblSTUDENTMARKS b On a.SubID = b.SubID
where a.StuId = 1 and b.StuID = 2


I will be getting the result as follows :

SlNo	StuID	SubID	StuMark	SlNo	StuID	SubID     StuMark

1 1 1 75 6 2 1 NULL
2 1 2 88 7 2 2 NULL
3 1 3 96 8 2 3 NULL
4 1 4 84 9 2 4 NULL
5 1 5 80 10 2 5 NULL


It clearly shows the table 'a' contains the details of stuent id 1 and table 'b' contains the details of student id 2.


Please note that I am checking table a.StuID = 1 and b.StuID = 2 in "where" condition. If we reverse the condition then marks of student-id 2 (here NULL) will be updated to marks of student-id 1 also.

So I am updating marks of the "b" table which of student id 2 with marks of "a" table which is of student id- 1.


Asked In: In my preivous interview | Alert Moderator 

Comments or Responses

Login to post response