I am trying to write a update that will update the value of one column in
several rows of data.  Basically I want to move a total into a column for
easier use later as follows.
In English it would be EMP_VARTOT=EMP_VARHRS - (SUM OF TAKEN THIS YEAR)
When I run the statement below I get the error
" 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."

update employee set emp_vartot = emp_varhrs - (select sum(psd_min)/60 as hours
from employee,emphis,psdetail,pslines,psheader
where psl_chh in (select chh_key from chrhis where chh_code='9910.ANNUAL
LEAVE') and
psd_date >= '20050101' and psd_psl=psl_key and psl_psh=psh_key and
psh_emh=emh_key and emh_emp=emp_key
group by emp_name)

The data is stored in PSDETAIL.PSD_MIN and that table relates to PSLINES,
which relates to PSHEADER, which then relates to the EMPHIS (employee
history) table which finally relates back to my EMPLOYEE table.

I know I could do this with a couple of statements or a program or TSQL, but
I'd like to know how to do it 1 statement so I can have it at my fingertips
next time.

Thanks

This only yields expected result if the subquery returns only a single row
("select sum(psd_min)/60 ...".

Think set-based. And please post DDL and sample data in order to get real
help.


ML

The error message is telling you that your subquery returns more than
one row for some rows in the employee table. You should remove the
GROUP BY because it is doing nothing useful for you. Probably you also
need some additional criteria in the WHERE clause. Of course this is
just total guesswork without enough info to reproduce the problem: DDL,
sample data, show your expected results.

Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?
SQL Count Problem