Suppose the following tables:
faculty:
+----+--------+
| id | headFK |
+----+--------+
department:
+----+----------+--------+
| id | facultyFK| headFK |
+----+----------+--------+
teacher:
+----+--------------+-------+
| id | departmentFK | chief |
+----+--------------+-------+
What is the best way to go through each row of teacher. Save some column values as variables (suppose: id
, departmentFK
) to use it in further Queries (Update to be specific)
My aim is to set up subordinations of teacher which is like this chain 'Faculty Head' > 'Department Head' > 'Teacher'
Formally it should look like:
FOR EACH ROW IN teacher
{
Set curDepartment = Current.departmentFK;
Set curTeacher = Current.id;
Set curFaculty = (Query to get faculty);
Set FacultyHead = (Query to get current faculty head by var curFaculty );
Set departmentHead = (Query to get current department head by var curDepartment );
(Query to update teacher.chief with conditions using variable acquired before)
}
Could you please tell the way (Procedures, Functions or ordinary queries) how can I do it because I've never had experience with procedures or local variables in MySQL and I cannot figure out how can I implement it
PS: I'm not asking to write code (that would be too much I think) but an approach to implement it
Update
Thanks to the Rick James's note I came up with the following solution:
UPDATE teacher
JOIN department ON teacher.DepFR = department.DepPK
JOIN faculty ON department.FacFK = faculty.FacPK
SET teacher.ChiefFK = CASE
WHEN teacher.TchPK = faculty.DeanFK THEN NULL
WHEN teacher.TchPK = department.HeadFK THEN faculty.DeanFK
ELSE department.HeadFK
END;
Is it a good way of doing it?
head
table (that theseheadFK
are referencing)?headFK
is referencingteacher
table. So aschief