Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-05
12
Medium Priority
?
496 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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

Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

721 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