Обсуждение: update only if single row
Here's the way I do it now:
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
command?
Frank
Frank,
The best I can come up with is using a temp table.
Something like:
select email into temp dummytable from contact where email ~* 'rvro' group by email having count(*) = 1;
update contact set bounce = 1 where email in (select email from dummytable);
It's still two queries. One use of your query, though.
Other than this, I would need to resort to a function.
Cheers,
Troy
>
> Here's the way I do it now:
>
> 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
> command?
>
> Frank
>
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
----- Original Message -----
From: "Leonid P. Klemjatsionok" <kl@84105.aanet.ru>
To: <pgsql-sql@postgreSQL.org>
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
> 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
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
>
>
>
MM> I'm not quite sure that the aliasing works properly in this query either - MM> does the EXISTS clause pick up the a alias? MM> UPDATE contact a SET a.bounce=1 WHERE a.email ~* 'rvro' AND NOT EXISTS MM> (SELECT id FROM contact b WHERE b.email ~*'rvro' AND a.id !=b.id); Aliasing does not work properly on v6.5. You can use full name. UPDATE contact SET bounce=1 WHERE email ~* 'rvro' AND NOT EXISTS(SELECT * FROM contact b WHERE b.email ~* 'rvro' AND contact.id!= b.id); Best regards, LPK Station mailto:kl@84105.aanet.ru
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
>
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
>