Solved

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

Posted on 2007-04-05
12
488 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:megnin
  • 7
  • 4
12 Comments
 
LVL 7

Expert Comment

by:jeepstyx22
ID: 18861752
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
 
LVL 1

Author Comment

by:megnin
ID: 18861762
Ahhh!  Thank you!  I'll give it a try.  I bumped the points up for the "after hours" quick response.  I appreciate it.
0
 
LVL 1

Author Comment

by:megnin
ID: 18861811
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 1

Author Comment

by:megnin
ID: 18861841
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
 
LVL 7

Accepted Solution

by:
jeepstyx22 earned 500 total points
ID: 18861866
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
 
LVL 7

Expert Comment

by:jeepstyx22
ID: 18861871
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
 
LVL 1

Author Comment

by:megnin
ID: 18863630
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
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 18863645
>>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
 
LVL 1

Author Comment

by:megnin
ID: 18864392
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
 
LVL 7

Expert Comment

by:jeepstyx22
ID: 18865059
See http://support.microsoft.com/kb/308049/en-us.  It has ExecuteScalar vs. ExecuteNonQuery as well as examples of calling each with parameters.  
0
 
LVL 1

Author Comment

by:megnin
ID: 18866542
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
 
LVL 1

Author Comment

by:megnin
ID: 18876046
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question