Self referencing update SQL statement for Informix

Question

Need some Informix SQL...

Courses get a regular grade, but their associated labs get a grade of 'LAB'. I need to update the table so that the lab grade matches the course grade. Also, if there is no corresponding course for a lab, it means the course was canceled. In that case, I want to place a flag value of 'X' for its grade.

Example data before update:

   id     yr sess crs_no                  hrs grd

  725   2009 FA   COLL101      3.000000000000 C
  725   2009 FA   ENGL021      3.000000000000 FI
  725   2009 FA   ENGL021L     1.000000000000 LAB
  725   2009 FA   ENGL031      3.000000000000 FNI
  725   2009 FA   ENGL031L     1.000000000000 LAB
  725   2009 FA   MATH010      3.000000000000 FNI
  725   2010 SP   AOTE101      3.000000000000 C
  725   2010 SP   ENGL021L     1.000000000000 LAB
  725   2010 SP   ENGL031      3.000000000000 FI
  725   2010 SP   ENGL031L     1.000000000000 LAB
  725   2010 SP   MATH010      3.000000000000 FNI
  726   2010 SP   SPAN101      3.000000000000 FN

Example data after update:

   id     yr sess crs_no                  hrs grd

  725   2009 FA   COLL101      3.000000000000 C
  725   2009 FA   ENGL021      3.000000000000 FI
  725   2009 FA   ENGL021L     1.000000000000 FI
  725   2009 FA   ENGL031      3.000000000000 FNI
  725   2009 FA   ENGL031L     1.000000000000 FNI
  725   2009 FA   MATH010      3.000000000000 FNI
  725   2010 SP   AOTE101      3.000000000000 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 worked out a solution for this, but it required a lot of on-the-fly composite foreign keys built from concatenating the id, yr, sess, and substring'd crs_no. My solution is not only overkill, but it has gaps in it and it takes too long to process.

I know there is an easier way to do this, but I've gone so far down one road that I am having trouble thinking of a different approach.

Answer 1

UPDATE lab
SET grd = NVL(crs.grd, 'X')
FROM Grades lab
LEFT JOIN Grades crs
    ON crs.id = lab.id
    AND crs.yr = lab.yr
    AND crs.sess = lab.sess
    AND crs.crs_no || 'L' = lab.crs_no
WHERE lab.grd = 'LAB'

Answer 2

UPDATE          det_list 
SET             grd = ( SELECT  c.grd 
                        FROM    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 stat NOT IN ('D','X') 
                                AND hrs > 0 
                                AND grd <> 'IP') 
                WHERE grd = 'LAB';

Answer 3

Would it make the composite key issue easier if you created a new column which holds the concatenated values of each column and create same column for the fk?.. Cheese with Cheese, did you ever try the AM/PM time programming code I provided you for your Ace report?