Received: from as.tksoft.com (gyw.com [209.55.67.177] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id SAA62042 for ; Fri, 7 Apr 2000 18:39:20 -0400 (EDT) (envelope-from tjk@tksoft.com) Received: (from tjk@tksoft.com) by uno.tksoft.com (8.8.8/8.8.8) id PAA02410; Fri, 7 Apr 2000 15:42:02 -0700 From: "tjk@tksoft.com" Message-Id: <200004072242.PAA02410@uno.tksoft.com> Subject: Re: update only if single row To: fbax@execulink.com (Frank Bax) Date: Fri, 7 Apr 2000 15:42:01 -0700 (PDT) Cc: pgsql-sql@postgresql.org In-Reply-To: <3.0.6.32.20000407153707.0107ab90@execulink.com> from "Frank Bax" at Apr 7, 0 03:37:07 pm Content-Type: text X-Archive-Number: 200004/77 It is supposed to update multiple rows. It is supposed to update all rows which have an email address matching the pattern ~* 'rvro', except rows where there are other rows with the same email address. Troy > > > Except for one minor detail. It updates multiple rows! > > At 02:39 AM 4/07/00 -0700, you wrote: > >Excellent solution. > > > >You can use oid as the unique attribute. > > > > UPDATE contact SET bounce = 1 WHERE email ~* 'rvro' AND email NOT IN > > (SELECT c1.email FROM contact c1, contact c2 > > WHERE c1.oid != c2.oid AND c1.email = c2.email); > > > >Troy > >> > >> 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); > >> > >> LPK Station mailto:kl@84105.aanet.ru >