Update Table data using variables depending on other tables

Question

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?

Answer 1

It is a no-no to have redundant data.

Instead, use JOIN when doing the SELECT; this will let you dynamically get the faculty, faculty_head, etc.

Learn about JOIN.