Self referencing update SQL statement for Informix -

Need some Informics SQL ...

courses to get regular grades, but their respective Laboratories 'LAB' grade I need to update the table so that the lab matches the grade course grade. Apart from this, if there is no such course for the laboratory, it means that the course was canceled. . In that case, I want to keep the flag value of 'x' for their grade

For example, before data updates:

  id year Sex GRD crs_no at 725 2009 FA COLL101 3.000000000000 C725 2009 FA ENGL021 3.000000000000 FI 725 2009 FA ENGL021L 1.000000000000 Laboratory 725 2009 FA ENGL031 3.000000000000 Fni 725 2009 FA ENGL031L 1.000000000000 Laboratory 725 2009 FA MATH010 3.00000000000000 Fni 725 2010 SP AOTE101 3.000000000000 C725 2010 SP ENGL021L 1.00000000000 Lab 725 2010 SP ENGL031 3.000000000000 FI 725 2010 SP ENGL031L 1.000000000000 laboratory 725 2010 SP MATH010 3.000000000000 Fni 726 2010 SP SPAN101 3.000000000000 FN  

For example, after data updates :

  id year sex crs_no at GD 725 200 9 FA COLL101 3.000000000000 C 725 200 9 FA Ianjiel 021 3.000000000000 FI 725 2009 FA Ianjiel 021 L 1.000000000000 FI 725 2009 FA ENGL031 3.000000000000 Fni 725 2009 FA ENGL031L 1.000000000000 Fni 725 2009 FA MATH010 3.00000000000000 Fni 725 2010 SP AOTE101 3.000 000000000 C 725 2010 SP ENGL021L 1.000000000000 x 725 2010 SP ENGL031 3.000000000000 FI 725 2010 SP ENGL031L 1.000000000000 FI 725 2010 SP MATH010 3.000000000000 Fni 726 2010 SP SPAN101 3.000000000000 FN  

I have worked out a solution for this, but overall foreign ID, year, sex is a lot of key on-the-fly created a series of requirements, and Substring'd crs_no My solution is not only redundancy , But there is a gap and this process Takes too long to receive.

I know that there is an easy way to do this, but I have gone a long way from a road. I am having trouble thinking about a different approach.

post

  update det_list set grd = (select c .grd to cw_rec c Where c.id = Det_list.id and c.sess = det_list.sess and c.yr = det_list.yr and c.crs_no = substr (det_list.crs_no, 0,7) and not in the stat ('D', 'X') and ' Gt; 0 and grd & lt;> 'ip') where grid = 'lb';