Self referencing update SQL statement for Informix
Posted
by CheeseConQueso
on Stack Overflow
See other posts from Stack Overflow
or by CheeseConQueso
Published on 2010-06-18T12:45:34Z
Indexed on
2010/06/18
15:13 UTC
Read the original article
Hit count: 308
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.
© Stack Overflow or respective owner
