Postgres Pro
Downloads
Home   >   mailing lists

Thread: trivial sql help

trivial sql help

From
Patrick Welche
Date:
I'm afraid I can't spot my error anymore... Any hints?

select pc.c,count(pc.c)
  from rawrequest,pers_comp as pc
 where pc.p=pid
 group by pc.c
having count(pc.c)=1
;

  c  | count
-----+-------
  16 |     1
  38 |     1
  59 |     1
  63 |     1
  83 |     1
  89 |     1
 128 |     1
 138 |     1
 146 |     1
 154 |     1
 156 |     1
 158 |     1
 203 |     1
 204 |     1
 221 |     1
 257 |     1
 314 |     1
 333 |     1
(18 rows)

update rawrequest
   set cid=(select pc.c
              from pers_comp as pc
             where pc.p=pid
             group by pc.c
            having count(pc.c)=1
           )
 where pr
;

ERROR:  More than one tuple returned by a subselect used as an expression.

pid, pc.p, pc.c, cid are all integers. pc just has 2 columns p and c.

I thought putting the count()=1 in there would force a single tuple..

Cheers,

Patrick

Re: trivial sql help

From
Joe Conway
Date:
Patrick Welche wrote:
>  333 |     1
> (18 rows)
>
> update rawrequest
>    set cid=(

> select pc.c
>   from pers_comp as pc
>  where pc.p=pid
>  group by pc.c
> having count(pc.c)=1
>            )
>  where pr
> ;
>
> ERROR:  More than one tuple returned by a subselect used as an expression.

Well, the same query above *does* return more than one row (18 in fact). I
think you need to qualify the sub-select a bit more, but without more details
on your schema, it is hard to guess how.

Joe

Re: trivial sql help

From
Stephan Szabo
Date:
On Thu, 10 Oct 2002, Patrick Welche wrote:

> update rawrequest
>    set cid=(select pc.c
>               from pers_comp as pc
>              where pc.p=pid
>              group by pc.c
>             having count(pc.c)=1
>            )
>  where pr
> ;
>
> ERROR:  More than one tuple returned by a subselect used as an expression.
>
> pid, pc.p, pc.c, cid are all integers. pc just has 2 columns p and c.
>
> I thought putting the count()=1 in there would force a single tuple..

No, that'll limit you only to groups having a count of 1.  It'll still
give multiple groups.  You could probably use limit to get 1 row, but
what's the actual behavior you want the update to have?

Re: trivial sql help

From
Patrick Welche
Date:
On Thu, Oct 10, 2002 at 09:12:28AM -0700, Joe Conway wrote:
>
> Well, the same query above *does* return more than one row (18 in fact).

Oh of course! Thank you!

Patrick

Re: trivial sql help

From
Patrick Welche
Date:
On Thu, Oct 10, 2002 at 09:14:41AM -0700, Stephan Szabo wrote:
>
> On Thu, 10 Oct 2002, Patrick Welche wrote:
>
> > update rawrequest
> >    set cid=(select pc.c
> >               from pers_comp as pc
> >              where pc.p=pid
> >              group by pc.c
> >             having count(pc.c)=1
> >            )
> >  where pr
> > ;
> >
> > ERROR:  More than one tuple returned by a subselect used as an expression.
> >
> > pid, pc.p, pc.c, cid are all integers. pc just has 2 columns p and c.
> >
> > I thought putting the count()=1 in there would force a single tuple..
>
> No, that'll limit you only to groups having a count of 1.  It'll still
> give multiple groups.  You could probably use limit to get 1 row, but
> what's the actual behavior you want the update to have?

pers_comp relates person to computer. 99% of students have 1 computer =>
only one pers_comp(p,c) entry. I just wanted to fill in the computer's id
into the request of a given person for the simple case.

Anyway, I was just having brain failure.. as you have all happily explained
away the error message, I'll try again..

Cheers,

Patrick