Update a table with a calculated value. "Subquery returned more than 1 value..."

I'm trying to update a table with a calculated value.  I have two tables, Applicants and JobTitles.
I want to update the PositionsAvailable field in JobTitles (current values may be NULL or some value)

This Select query gives me the numbers I need:
SELECT     JobTitles_1.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS Expr1
FROM         Applicants INNER JOIN
                      JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID

I tried this query and although it pased the "Check SQL" check I got an error when I tried to execute it:
UPDATE    JobTitles
SET              PositionsAvailable =
                          (SELECT     JobTitles_1.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS Expr1
                            FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)

The error said "...Subquery returned more than 1 value.  This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression..."

Can someone help me fix it?  I'm using VB.NET, VS2005, SQL Server 2000
thanks
LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jeepstyx22Commented:
Try adding a where clause at the end as shown below.  I assume your query returns multiple rows. The where clause will direct sql as to what it is supposed to update.
UPDATE    JobTitles
SET     PositionsAvailable =
           (SELECT JobTitles_1.txtWorksiteJobTitlePositionsNumber -COUNT(Applicants.keyApplicantID)
FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)
WHERE JobTitles.keyJobTitleID=JobTitles_1.keyJobTitleID
0
megninAuthor Commented:
Ahhh!  Thank you!  I'll give it a try.  I bumped the points up for the "after hours" quick response.  I appreciate it.
0
megninAuthor Commented:
It gives me a "The column prefix JobTitles_1 does not match a table or alias used in the query" error.
That's odd because JobTitles_1.keyJobTitleID is used all over the place in the query.
I'm just learning SQL and working on my production database so I don't want to "experiment" too much.
Would this work if I used:
WHERE JobTitles.keyJobTitleID=Applicants.keyJobTitleID instead of
WHERE JobTitles.keyJobTitleID=JobTitles_1.keyJobTitleID  ?

The two tables I'm working with are Applicants and JobTitles.

Thanks again.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

megninAuthor Commented:
Okay, I tried:  WHERE JobTitles.keyJobTitleID=Applicants.keyJobTitleID and got the same error... "Applicants doesn't match a table used in the query..."
So I moved the parentheses to the end putting:  "WHERE JobTitles.keyJobTitleID=Applicants.keyJobTitleID" inside the subquery and got an "Error near 'Where' ".
0
jeepstyx22Commented:
You may want to make a backup of the database before trying to much, just so you can go back.  Try the query below.  It moves the where clause within the subquery.

UPDATE JobTitles
SET PositionsAvailable = (SELECT JobTitles_1.txtWorksiteJobTitlePositionsNumber-COUNT(Applicants.keyApplicantID)
FROM Applicants INNER JOIN JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
WHERE JobTitles.keyJobTitleID=JobTitles_1.keyJobTitleID
GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeepstyx22Commented:
In regards to the where clause error; you have to do Where Clauses prior to Group By.

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
0
megninAuthor Commented:
I was wondering if the order mattered.  I had tried putting it in the subquery, but only by moving the parentheses, not moving the clause.  Thanks for that tip!
I did back up my database last night before I tried it the first time.  I'll back it up again before I try this one too.  ;-)
0
JohnBPriceCommented:
>>The column prefix JobTitles_1 does not match a table

The WHERE clause must be within the parens, not outside, like this

UPDATE    JobTitles
SET     PositionsAvailable =
           (SELECT JobTitles_1.txtWorksiteJobTitlePositionsNumber -COUNT(Applicants.keyApplicantID)
FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID
               WHERE JobTitles.keyJobTitleID=JobTitles_1.keyJobTitleID)
0
megninAuthor Commented:
That worked like a charm.  Thank you very much!!

I saved it as a StoredProcedure.  Could you give me a quick instruction on what's required to execute the StoredProcedure from a Button_Click event.

...
Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = " my connection string "
cmd = StoredProcedure1
con.Open()
cmd.ExecuteScalar
con.Close()
...

Is that close to being correct?  " ...'Scalar, 'NonScalar"?
Are there "standard" error handling bits that I should include as a general rule?

Thanks again.
0
jeepstyx22Commented:
See http://support.microsoft.com/kb/308049/en-us.  It has ExecuteScalar vs. ExecuteNonQuery as well as examples of calling each with parameters.  
0
megninAuthor Commented:
Is this correctly written?

        Dim con As New SqlConnection
      Dim cmd As SqlCommand = New SqlCommand & _
          ("spUpdate_JobTitles_PositionsAvailable", con)
        con.ConnectionString = "myConnectionString"
        cmd.Connection = con
      cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        cmd.ExecuteNonQuery
        con.Close()

0
megninAuthor Commented:
Oh, spUpdate_JobTitles_PositionsAvailable is an Update query:

UPDATE    JobTitles
SET              PositionsAvailable =
                          (SELECT     JobTitles_1.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS Expr1
                            FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            WHERE      (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)

It's attached to a button_click event.

When I tested it, it did not update
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.