mySQL update query on join

Question

I have a countries database and another table called zones.

Countries 
[id, name, status (1 enabled 0 disabled) ]


Zones 
[id, name, country_id]

I am using the following query to match up all countries with their zone.

select 
z.name as state, 
z.id as state_id, 
c.name as country_name, 
c.id as country_id, 
c.status as country_status 
from countries c left join zones z on c.id = z.country_id 

So basically in short a Zone is the state and the output is like this.

+-----------------------------------------------------+----------+---   -----------------------------------------+------------+----------------+
| state                                               | state_id | country_name                               | country_id | country_status     |
+-----------------------------------------------------+----------+---  -----------------------------------------+------------+----------------+
| NULL                                                |     NULL | Christmas Island                           |         45 |              1   
| NULL                                                |     NULL | Puerto Rico                                |        172 |              1    
| NULL                                                |     NULL   Isle of Man                                |        254 |              1  
| Álava                                        |     2971 | Spain                                      |        195 |              1   
| Ávila                                        |     2976 | Spain                                      |        195 |              1 
| Évora                                        |     2656 | Portugal                                   |        171 |              1   

The output is huge to paste here so only showing in the end of the result

I want to update the status on countries to 0 where there is no zone. Any idea how I can do this via mySQL?

Answer 1

you can use not in like this:

 update Countries set status=0 where id not in (select distinct country_id from Zones )