0

I apologize for this dumb question but need someone to explain in simple terms as to whats the difference and why the 1st option works the best.

Question: I have a table named student, table structure as shown below

Student_id   Student_name    Subject1_marks    Subject2_marks   
001           John              11                    0 
002           Barack            12                    1 
003           McCain            12                    0

Now, I need the Subject1_marks to be set into subject2_marks where Subject2_marks equals 0

The SQL's that I wrote are:

1st SQL:

update student set Subject2_marks = Subject1_marks
where Subject2_marks= 0;

2nd SQL:

update student a set a.Subject2_marks=b.Subject1_marks from student b
where a.student_id=b.student_id
and a.Subject2_marks= 0;

For me the 1st SQL worked perfectly fine, but the 2nd did not, need help in understanding why the 2nd did not work.

Any explanation in simple terms will be greatly appreciated.

Thanks,

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51
user3040077
  • 71
  • 2
  • 10
  • :The syntax is wrong for the second sql as explained below ,the first sql is correct and no need to self join with the same table is required for such simple update – Gaurav Soni Dec 17 '13 at 17:38
  • 1
    @user3040077 "need help in understanding why the 2nd did not work" because syntax is just not correct. You should use subquery for such things. But in this particular case it would be overkill, just stick with your first method. – Yaroslav Shabalin Dec 17 '13 at 21:25

1 Answers1

1

You specified a.Subject2_marks = 0 . But missed the other table for this column. So for that student_id, all rows in the other table will be joined, even if the mark is is non zero, the column will be updated again n again.. with the last update staying finally!

Student_id   Student_name    Subject1_marks    Subject2_marks   
001           John              11                    0 
001           John              13                    2 
002           Barack            12                    1 
003           McCain            12                    0

In the above data 001 Student_id has 2 mark details , what happened is, with left side table having 001-11-0 qualified, the other side has 001-11-0,001-13-2

so, A's 1 row will be updated two times in this join once with mark 11 and next with mark 13. The order can't be judged!.

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • Thanks for the response Maheswaran, appreciate your time. Can you give an example of a scenario where a self join would be used or will be needed to be used. – user3040077 Dec 17 '13 at 22:11
  • Welcome! An example with respect to your case itself. when you want to retrieve all the marks of a `student_id` who scored `0` in `Subject2_marks`. Here since both `student_id` and `Subject2_marks` are in same table, we have to `SELF JOIN` them. And your Query actually did this only. Thus it went wrong! – Maheswaran Ravisankar Dec 17 '13 at 22:20
  • Finally, if satisfied, please accept this as answer :) – Maheswaran Ravisankar Dec 17 '13 at 22:21