Mysql – Update Table data using variables depending on other tables


Suppose the following tables:

| id | headFK |

| id | facultyFK| headFK |

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

    Set curDepartment = Current.departmentFK;
    Set curTeacher =;
    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


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

Is it a good way of doing it?

Best Answer

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.