• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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
0
megnin
Asked:
megnin
  • 7
  • 4
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now