I have two similar tables with host and services availability
data (Nagios). And I want to find out services which are not OK
in first table and OK in second one. Query I used is:
select c.* from coll_servicestatus as c inner join
servicestatus as s on (c.service_description=s.service_description
and c.host_name=s.host_name) where c.service_status != 'OK' and
s.service_status = 'OK';
Results are fine. But how to write UPDATE statement if I want
to set those non-OK states in first table to OK if they are OK
in the second table?
Something like:
update coll_servicestatus set service_status = 'OK' from
coll_servicestatus as c, servicestatus as s where
c.service_description = s.service_description and c.host_name=s.host_name
and c.service_status != 'OK' and s.service_status = 'OK';
updates all rows not only required ones.
Mindaugas
