Re: 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);

LPK Station mailto:kl(at)84105(dot)aanet(dot)ru