Re: update only if single row
От | tjk@tksoft.com |
---|---|
Тема | Re: update only if single row |
Дата | |
Msg-id | 200004080532.WAA03784@uno.tksoft.com обсуждение исходный текст |
Ответ на | update only if single row (Frank Bax <fbax@execulink.com>) |
Список | pgsql-sql |
Frank, My apologies. You are right. I read "if a single email is selected" and not "if a single row is selected," like it says. A function seems like the only logical place to go. Just for the curiosity, though, here is a set of two queries which handle the job: SELECT max(email) as email into temp dummy from contact where email ~* 'rvro' having count(*) = 1; UPDATE contact set bounce = 1 where email in (select email from dummy); If you were able to use the first query as a subquery, then it would be just one query. I don't see how to do that, though. Troy > > Tony: > > No it is NOT supposed to update multiple rows. Please re-read the > requirements. > > >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 > >command? > > The SQL statement should update the row with email address matching pattern > ~* 'rvro' ONLY if a single row matches that pattern. If multiple rows > match the pattern, the update should NOT be performed. > > Frank > > > At 03:42 PM 4/07/00 -0700, you wrote: > >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 > >> >> > >> >> 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 >
В списке pgsql-sql по дате отправления: