From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Mindaugas Riauba <mind(at)bi(dot)lt> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SQL syntax |
Date: | 2002-07-31 15:57:32 |
Message-ID: | 20020731085358.X17229-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | Chad R. Larson | 2002-07-31 18:22:55 | Re: 3-tier |
Previous Message | Mindaugas Riauba | 2002-07-31 13:16:27 | SQL syntax |