Oracle SQL: update table conditionally based on values in another table

Question

[Previous essay-title for question]

Oracle SQL: update parent table column if all child table rows have specific value in a column. Update RANK of only those students who have 100 marks in all the subjects. If student has less than 100 marks in any subject, his RANK should not be updated.

I have a scenario where I have a parent table and a child table. The child table has a foreign key to parent table. I need to update parent table's status column when a column in child table rows have specific values. There are more than one child records for each parent, in some cases none. Is it possible to achieve this with Oracle SQL, without using PL/SQL. Is that possible, can some one explain how? In some case I have to update parent table row's column based on two columns of child table records.

My exact problem is like : I have two tables STUDENTS, MARKS. MARKS has a FK to STUDENTS named STUDENT_ID.MARKS has number of rows for a STUDENT record, depending on different subjects (MARKS has a FK to SUBJECTS), and has a column named MARKS_OBTAINED. I have to check that if MARKS_OBTAINED for one student for every subject (i.e. all his records in MARKS) have value 100, then update STUDENT table's column RANK to a value 'Merit'. This query:

update STUDENT
      set RANK = 'Merit'
      where   exists ( select *
                         from MARKS
                        where MARKS.STUDENT_ID = STUDENT.ID
                          and MARKS.MARKS_OBTAINED  = 100)
      and not exists ( select *
                         from MARKS
                        where MARKS.STUDENT_ID = STUDENT.ID
                          and MARKS.MARKS_OBTAINED != 100)

updates all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks. Because it passes rows for a STUDENT in MARKS where one record in MARKS has 100 MARKS_OBTAINED but other records have less than 100 marks, but since STUDENT obtained 100 marks in one subject, its RANK will also get updated. The requirement is that if any STUDENT records has a MARKS record with non 100 value in MARKS_OBTAINED column this STUDENT record should get excluded from the query.

Answer 1

Your question is a little too vague at the moment to really answer fully. What happens to a parent row if it has no children? What happens if some of the child rows have specific values but not all of them? In the two-column case, what combinations of number of children/values are needed (is is the same set of values for each column or unique ones? Is it an AND relationship or an OR relationship)? Etc...

Anyway, making the assumption that there needs to be at least one child row with a value in a given domain, this should be fairly straightforward:

update PARENT set STATUS = 'whatever'
 where ID in (
     select parent_id from CHILD
      where value_col in ('your', 'specific', 'values', 'here')
 );

This general pattern expands to the multi-column case easily (just add an extra AND or ORed condition to the inner where clause), and to the negative case too (change where ID in to where ID not in).

If performance of this update is an issue you may want to look at triggers - at the price of slightly slower inserts on the child tables, you can keep your parent table up-to-date on an ongoing basis without having to run this update statement periodically. This works quite nicely because the logic of inspecting each child row is essentially distributed across each individual insert or update on the child table. Of course, if those child modifications are performance-critical, or if the child changes many times in between the points where you need to update the parent, then this wouldn't work very well.

Answer 2

What about:

UPDATE ParentTable
   SET StatusColumn = 78
 WHERE PK_Column IN
       (SELECT DISTINCT FK_Column
          FROM ChildTable AS C1
         WHERE (SELECT COUNT(*) FROM ChildTable C2
                 WHERE C1.FK_Column = C2.FK_Column) =
               (SELECT COUNT(*) FROM ChildTable C3
                 WHERE C1.FK_Column = C3.FK_Column
                   AND C3.OtherColumn = 23)
       )

I strongly suspect there are neater ways to do it, but...the correlated sub-queries count the number of rows in the child table for a particular parent and the number of rows in the child table for the same parent where some filter condition matches a particular value. Those FK_Column values are returned to the main UPDATE statement, giving a list of primary key values for which the status should be updated.

This code enforces the stringent condition 'all matching rows in the child table satisfy the specific condition'. If your condition is simpler, your sub-query can be correspondingly simpler.

Answer 3

Total rewrite

This is a complete rewrite to fit my example to the OQ's revised question. Unfortunately Manish has not actually run my original solution otherwise they would realise the following assertion is wrong:

Your solution returns all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks.

Here are six students and their marks.

SQL> select * from student
  2  /

        ID RANK
---------- ----------
         1 normal
         2 normal
         3 normal
         4 normal
         5 normal
         6 normal

6 rows selected.

SQL> select * from marks
  2  /

 COURSE_ID STUDENT_ID       MARK
---------- ---------- ----------
         1          1        100
         2          1        100
         1          2        100
         2          2         99
         1          4        100
         2          5         99
         1          6         56
         2          6         99

8 rows selected.

SQL>

Student #1 has two courses with marks of 100. Student #4 has just the one course but with with a mark of 100. Student #2 has a mark of 100 in one course but only 99 in the other course they have taken. None of the other students scored 100 in any course. Which students will be awarded a 'merit?

SQL> update student s
  2      set s.rank = 'merit'
  3      where exists ( select null
  4                     from marks m
  5                     where m.student_id = s.id
  6                     and m.mark = 100 )
  7      and not exists ( select null
  8                       from marks m
  9                       where m.student_id = s.id
 10                       and m.mark != 100)
 11  /

2 rows updated.

SQL>
SQL> select * from student
  2  /

        ID RANK
---------- ----------
         1 merit
         2 normal
         3 normal
         4 merit
         5 normal
         6 normal

6 rows selected.

SQL>

And lo! Only those students with 100 marks in all their courses have been updated. Never underestimate the power of an AND.

So the teaching is: an ounce of testing is worth sixteen tons of supposition.