On Wed, 31 Jul 2002, Mindaugas Riauba wrote:
> > 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. There are two joins on coll_servicestatus in the above, the one in the from and one with the updating table. You could probably remove the coll_servicestatus as c, and change references to c to coll_servicestatus or use a subselect, something like: update coll_servicestatus set service_status='OK' where service_status!='OK' and exists (select * from servicestatus as s where coll_servicestatus.service_description=s.service_description and coll_servicestatus.host_name=s.host_name and s.service_status='OK'); ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
