Mysql code to trigger update on column from different table

Question

I am using a database that has 2 tables for city and country. City table has ID(PK), population and country code. Country table has Code(PK) and population. If an update happens to city population, the country population should be updated automatically.The issue is how to implement this in a proper update statement. It should follow the general pattern below:

  CREATE TRIGGER diff_pop
  AFTER UPDATE 
  ON city
  FOR EACH ROW 
  ...
  UPDATE country SET NEW.population=country.population-city_old+city_new 
WHERE country.population=new.population;
   

I think there could be many ways to solve this, so any input is appreciated.

Answer 1

CREATE TRIGGER diff_pop
AFTER UPDATE 
ON city
FOR EACH ROW
UPDATE country 
SET population = population - OLD.population + NEW.population 
WHERE country.code = NEW.countrycode;

If it is possible that the city may be moved from one counntry to another then

CREATE TRIGGER diff_pop
AFTER UPDATE 
ON city
FOR EACH ROW
BEGIN
    UPDATE country 
    SET population = population - OLD.population 
    WHERE country.code = OLD.countrycode;
    UPDATE country 
    SET population = population + NEW.population 
    WHERE country.code = NEW.countrycode;
END

PS. Do not forget to create AFTER INSERT and AFTER DELETE triggers...

---

That does not work, it is ambiguous syntax according to mysql, since there is no distinction between population column on city and country. – seneca

fiddle. Try to refute it.