How to search data from a third table based on the associative table

Question

To put it in context: I have a table called principal and another called school. There are schools that directors who are registered with the name of the school. In this case, these schools are registered in the school table and the association between principal and school is made by a school associative table. Where I take the id of the two and form the association in the table.

Doubt: How do I update in the school table the emails of the respective schools (principals with school names) that are in the master table? The id’s are not the same and the name is also not written in the same way in both tables. I only have the associative table as support for this.

Table structure:

CREATE TABLE IF NOT EXISTS `diretor` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(250) NOT NULL,
  `nome` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `diretor_escola` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idDiretor` int(10) unsigned NOT NULL,
  `idEscola` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idDiretor_idEscola` (`idDiretor`,`idEscola`),
  CONSTRAINT `idDiretor` FOREIGN KEY (`idDiretor`) REFERENCES `diretor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_idEscola` FOREIGN KEY (`idEscola`) REFERENCES `escola` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `escola` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(250) NOT NULL,
  `codigo` varchar(250) NOT NULL,
  `email` varchar(250) NOT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

Answer 1

Basically you will use the keys of the tables to link them. I have set an example without knowing the name of the columns, but it is something like:

UPDATE e
   SET e.email = d.email
  FROM escola e
       INNER JOIN escola_diretor ed ON ed.idEscola = e.id
       INNER JOIN diretor d ON d.id= ed.idDiretor