limit limit - 2 years ago 124
PHP Question

mySQL update query on join

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 Source

you can use not in like this:

 update Countries set status=0 where id not in (select distinct country_id from Zones )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download