simple stored procedure

[site]

Hello, All,

This is my stored procedure, but doesn't work. The error is: "subquery returned more than one value".

CREATE PROCEDURE qrycount AS

update complaints

set num_of_complaints =

(select count(compli_num)
from complaints where compli_num is not null group by compli_num )
GO


Thank you very much for your help.

Jing

 

[foxdev]

You are trying to set a single column to multiple values. Your subselect:

[tt]select count(compli_num)
from complaints where compli_num is not null group by compli_num [/tt]

is returning more than one row, because you have multiple unique compli_num values and you have a group by compli_num clause.

Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[bassguy]

Hi,
if you are just trying to count the total number of compli_num you do not need the group by clause. the group by clause is seperating your count(compli_num) into different kinds of compli_num.


try just running your subquery you will see what I mean

select count(compli_num)
from complaints where compli_num is not null group by compli_num

will give you several rows of results


bassguy

 

[tlbroadbent]

You need to relate the subquery to the table you are updating in order to get one value per compli_num in the table. The following should work.

Update complaints
Set num_of_complaints = b.NoComp
From complaints Inner Join
(Select compli_num, count(compli_num) As NoComp
From complaints
Where compli_num is not null
Group By compli_num) As b
On Complaints.compli_num=b.compli_num Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin