Обсуждение: trivial sql help
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
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
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?
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
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