another sql query ?

I'm trying to do a mass update, w/different values: I have an Approval Table with the columns, FacilityID, CompanyID, EmployeeID, and SupervisorID. I would like to take the Users SuperID in the [User] Table who have a roleid= 'Supervisor' and place it in the Approval Table. However in order to know which super matches which employee you must match the Employee tables DeptID to the User Tables Department field.

I did the following and it gives me an error:

UPDATE Approval SET SupervisorID = (select u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')

The error is:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The select gives me the right data--this much I've checked.

You sub-query is probably returning more than 1 row in some cases. You have to make sure you only get one result row. Try:

UPDATE Approval SET SupervisorID = (selectTOP 1u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')|||Well see that's what I'm confused about--I would like to update all of the SupervisorID's in the table at once. There are 125 rows in the Approval Table, and about 10 different superviosrID's in the User Table. Am I going about this wrong?|||

You have to make sure to provide a condition where the table you're updating has one-to-one relationship with the sub-query value returned for each row. In your query, you have nothing that defines the relationship between the Approval table (one you're updating) and other tables in the sub-query. I can't tell you what it should look like since I have no clue on what the tables look like. There must be some column in the Approval table that links to other tables being referenced in your sub-query, so provide that relationship in your where clause.

|||

You've almost got it. Problem is your subselect is returning ALL the supervisors for every department. Your statement therefore is trying to set the Supervisor in the Approval table to every supervisor, which...You can't do.

You want to set the supervisor in the approval table to a specific supervisor. In order to do that, your subselect needs to know how it relates to the rows in the approval table. In english, that means you need something else in your WHERE clause in the subselect, something along the lines of... AND e.EmployeeID=approval.EmployeeID.

|||I see I got it now--your addition of query code worked great. Appreciate the help.|||Ok--since I'm stuck I might as well keep on asking--this is a new SQL query I have an SP already and it's the following:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
LEFT JOIN EmployeeEval ev --this left join will return all rows from Employee, but only rows from Eval where the employee is in.
ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = @.Period
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND (e.DeptID = @.deptID) and Status = 'Active'
GO

So now I need to incorporate my Approval Table, we're trying to get rid of being dependent on finding employees based on Dept, b/c we need some supervisors to be in charge of multiple departments.

So I need to Pull back the Info in the SELECT above and keep the EmployeeEval stuff. As well I need to remove the Dept data at the bottom of the above query. My approval table has the following columns: FacilityID, CompanyID, EmployeeID, and SupervisorID so I was thinking do something like this:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e LEFT JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = 175
INNER JOIN Approval a ON e.employeeid = a.employeeid
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND e.Status = 'Active' AND a.supervisorID = '09246'
GO

But it's returning 0 rows--I have one person in the approval table with supervisorID set to '09246'. they have no data in the employeeeval table. But in my where clause i have the ev.employeeid is null which should still allow 4 them to be brought back--let me know if this doesn't make sense.|||well I guess my query was right--I just didn't have my settings in the new table set up right--it's now showing up. thanks for all the help.