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

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.