Received: from oxmail.ox.ac.uk (oxmail3.ox.ac.uk [129.67.1.180]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA09498 for ; Fri, 7 Apr 2000 04:48:05 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 3.12 #1) id 12dUQu-0005G0-00; Fri, 07 Apr 2000 09:48:04 +0100 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 3.13 #1) id 12dUQu-0003f5-00; Fri, 07 Apr 2000 09:48:04 +0100 Message-ID: <006001bfa06d$d45f5480$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "PGSQL SQL" , "Frank Bax" References: <3.0.6.32.20000406132216.008a8420@execulink.com> Subject: Re: update only if single row Date: Fri, 7 Apr 2000 09:46:56 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200004/61 > select * from contact where email ~* 'rvro'; > if I get a single row in the result then I enter: > update contact set bounce=1 where email ~* 'rvro'; > > Can I combine this into a single SQL statement with the following > requirements: > 1) the update is only performed if a single row is selected > 2) I only enter the selection string ('rvro' in this case) once in the If you have some kind of id field in the table, you can do it like this, or you can use the oids, but I don't know how to write that :-> I'm not quite sure that the aliasing works properly in this query either - does the EXISTS clause pick up the a alias? UPDATE contact a SET a.bounce=1 WHERE a.email ~* 'rvro' AND NOT EXISTS (SELECT id FROM contact b WHERE b.email ~*'rvro' AND a.id !=b.id); Yours, Moray