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 EAA08906 for ; Fri, 7 Apr 2000 04:40:46 -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 12dUJi-00053w-00; Fri, 07 Apr 2000 09:40:38 +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 12dUJi-000010-00; Fri, 07 Apr 2000 09:40:38 +0100 Message-ID: <004001bfa06c$ca5c9340$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "Leonid P. Klemjatsionok" , References: <3.0.6.32.20000406132216.008a8420@execulink.com> <14478.000407@84105.aanet.ru> Subject: Re: update only if single row Date: Fri, 7 Apr 2000 09:39:29 +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/60 ----- Original Message ----- From: "Leonid P. Klemjatsionok" To: Sent: Friday, April 07, 2000 8:28 AM Subject: Re: [SQL] update only if single row > Hi > > FB> select * from contact where email ~* 'rvro'; > FB> if I get a single row in the result then I enter: > FB> update contact set bounce=1 where email ~* 'rvro'; > > FB> Can I combine this into a single SQL statement with the following > FB> requirements: > FB> 1) the update is only performed if a single row is selected > FB> 2) I only enter the selection string ('rvro' in this case) once in the > FB> command? > > Assume that pk is PRIMARY KEY (or UNIQUE attribute) of relation contact. > > UPDATE contact SET bounce = 1 WHERE email ~* 'rvro' AND NOT email IN > (SELECT c1.email FROM contact c1, contact c2 > WHERE c1.pk != c2.pk AND c1.email = c2.email); Surely this is not the same thing at all? This potentially updates several rows, where there are several non-identical email addresses which all contain rvro. The original two querys only update the row where there is only one email address which contains rvro. Yours, Moray ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk