Solved

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

Posted on 2007-04-05
12
490 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing Version of .NET in Windows 10 17 138
Difference between Leaflet and MapBox? 5 47
Make borderless form movable by user 2 27
Word formfield existence 3 16
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

710 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