to update multiple values in a single query

[palani12kumar]

Hi.....
I've a table that contains the students marks.
The fields in the table are:
Sno,m1,m2,m3,m4,m5,total,avg,grade.
im having ten records in the table in which, i've to update the grade column such that:
if the avg if above 80, the grade should be 'first' and 'second' if not.
I've to accomplish this in a single query(nested).
I dont know how to implement this.
Please some body help me in my problem????!!!!!!!!!!!!!!!!

[Dave44]

Hi.....
I've a table that contains the students marks.
The fields in the table are:
Sno,m1,m2,m3,m4,m5,total,avg,grade.
im having ten records in the table in which, i've to update the grade column such that:
if the avg if above 80, the grade should be 'first' and 'second' if not.
I've to accomplish this in a single query(nested).
I dont know how to implement this.
Please some body help me in my problem????!!!!!!!!!!!!!!!!

So you are to update that field each time a new mark is added (eg when m2 is populated and again when m3 is populated and so forth)?

also is each m# field null until the mark is calculated? (just dont want to assume here)

[debasisdas]

For this u can use CASE

try this piece of code


update student set grade =
CASE
WHEN avgmark>70 THEN 'A'
WHEN avgmark>60 THEN 'B'
WHEN avgmark>50 THEN 'C'
ELSE
'D'
END

it updates all the rows .


hope it helps u