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

Question

[Previous essay title for the question]

Oracle SQL: update the parent column of a table if all rows in the child tables have a specific value in the column. Update RANK only for students who have 100 points in all subjects. If a student has less than 100 points 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 the parent table. I need to update the status column of the parent table when the column in the child row tables has certain values. There are several child records for each parent, in some cases not. Is it possible to achieve this with Oracle SQL without using PL / SQL. It is possible, can someone explain how? In some cases, I need to update a row column of a parent table based on two columns of records in child tables.

My specific problem is this: I have two tables of STUDENTS, SIGNS. MARKS has an FK for STUDENTS named STUDENT_ID.MARKS has a number of rows for writing STUDENT, depending on the different items (MARKS has an FK for SUBJECTS) and has a column named MARKS_OBTAINED. I have to verify that if MARKS_OBTAINED for one student for each subject (i.e., all of his records in MARKS) is set to 100, then update the column of the STUDENT RANK table to “Merit”. This request:

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)

, 100 . , 100 . MARKS, MARKS 100 MARKS_OBTAINED, 100 , STUDENT 100 , RANK . , - STUDENT MARKS 100 MARKS_OBTAINED, STUDENT .

Answer 1

, OQ. , Manish , , :

, 100 . 100 .

.

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>

№ 1 100. № 4 , 100. №2 100 , 99 , . 100 . ""?

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>

! , 100 . AND.

, : .

Answer 2

, . , ? , , ? / ( ? AND OR)? Etc...

, , , :

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

( AND ORed inner where), ( where ID in where ID not in).

, - , . , . , , , .

Answer 3

:

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)
       )

, , ... , . FK_Column UPDATE, , .

: " ". , .