How to update on cascade in MySQL?

Here's a solution that uses cascading foreign keys to do what you describe:

mysql> create table city (  id int not null auto_increment,   name varchar(45),   active tinyint,   primary key (id),  unique key (id, active));mysql> create table person (  id int not null auto_increment,   city_id int,  active tinyint,   primary key (id),   foreign key (city_id, active) references city (id, active) on update cascade);mysql> insert into city (name, active) values ('New York', 1);mysql> insert into person (city_id, active) values (1, 1);mysql> select * from person;+----+---------+--------+| id | city_id | active |+----+---------+--------+|  1 |       1 |      1 |+----+---------+--------+mysql> update city set active = 0 where id = 1;mysql> select * from person;+----+---------+--------+| id | city_id | active |+----+---------+--------+|  1 |       1 |      0 |+----+---------+--------+

Tested on MySQL 5.5.31.

Maybe you should reconsider how you define a person to be active.. Instead of having active defined twice, you should just keep it in the city table and have your SELECT statements return Person WHERE city.active = 1..

But if you must.. you could do something like:

UPDATE city CLEFT JOIN person P ON C.id = P.citySET C.active = 0 AND P.active = 0WHERE C.id = @id