Received: from ns2.bee.lt (ns.bi.lt [213.226.131.131])
	by postgresql.org (Postfix) with ESMTP id D782D474EDC
	for <pgsql-sql@postgresql.org>; Wed, 31 Jul 2002 09:16:42 -0400 (EDT)
Received: from B027543 ([213.226.131.30])
	by ns2.bee.lt (8.10.0/8.10.0) with SMTP id g6VDGkp15237
	for <pgsql-sql@postgresql.org>; Wed, 31 Jul 2002 15:16:46 +0200
Message-ID: <06d401c23894$7d9b4c00$f20214ac@B027543>
From: "Mindaugas Riauba" <mind@bi.lt>
To: <pgsql-sql@postgresql.org>
Subject: SQL syntax
Date: Wed, 31 Jul 2002 15:16:27 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1257"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200207/458
X-Sequence-Number: 8836


  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