asked on

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

jeepstyx22

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

David Megnin

ASKER

Ahhh! Thank you! I'll give it a try. I bumped the points up for the "after hours" quick response. I appreciate it.

David Megnin

ASKER

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.

David Megnin

ASKER

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' ".

ASKER CERTIFIED SOLUTION

jeepstyx22

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jeepstyx22

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

David Megnin

ASKER

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. ;-)

JohnBPrice

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

David Megnin

ASKER

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.

jeepstyx22

See http://support.microsoft.com/kb/308049/en-us. It has ExecuteScalar vs. ExecuteNonQuery as well as examples of calling each with parameters.

David Megnin

ASKER

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

David Megnin

ASKER

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

Update a table with a calculated value. &quot;Subquery returned more than 1 value...&quot;

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