UPDATE sql statement help please [message #317637] |
Fri, 02 May 2008 04:54 |
sunildatt_b
Messages: 3 Registered: May 2008
|
Junior Member |
|
|
question
==========
The company has decided to adopt a bonus policy for their employees. Each employee will now receive a bonus of $500 for every project on which they have charged at least 10 hours. As a result, management has asked you to update the employee records to include their current bonus information. In order to accomplish this, you will have to add another attribute to the EMPLOYEE table, this attribute is BONUS_AMT. You will then have to update the value in the bonus amount field for each employee to be equal to $500 multiplied by the number of projects on which they have charged more than 10 hours.
the query i wrote is
=============
update EMPLOYEE E SET e.BONUS_AMT = (select count(*) * 500
from ASSIGNMENT a, EMPLOYEE E where e.emp_num =e.emp_num group by e.emp_num
having SUM(a.HOURS_CHARGED) > 10 )
where e.BONUS_AMT =0
but its showing error like
ora-01427: single row subquery returns more than one row
can you help me to update this employee table
|
|
|
|
Re: UPDATE sql statement help please [message #317662 is a reply to message #317637] |
Fri, 02 May 2008 06:07 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try this:update EMPLOYEE E SET e.BONUS_AMT = (select count(*) * 500
from ASSIGNMENT a where a.emp_num =e.emp_num group by a.emp_num
having SUM(a.HOURS_CHARGED) > 10 )
where e.BONUS_AMT =0
You didn't need the EMPLOYEE table inside the sub-query, and giving the table inside the query the same alias as the one outside it is only going to cause problems.
|
|
|
Re: UPDATE sql statement help please [message #317665 is a reply to message #317637] |
Fri, 02 May 2008 06:18 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | Each employee will now receive a bonus of $500 for every project on which they have charged at least 10 hours
|
Quote: |
update EMPLOYEE E SET e.BONUS_AMT = (select count(*) * 500
from ASSIGNMENT a, EMPLOYEE E where e.emp_num =e.emp_num group by e.emp_num
having SUM(a.HOURS_CHARGED) > 10 )
where e.BONUS_AMT =0
|
I am not able to see any grouping been done on the project level. I think it should be on the employee + project level.
Brief explanation why I think it should be like this.
create table assignment
(empno number,
project_id number,
hours_worked number
)
nologging;
insert into assignment values (1,1,10);
insert into assignment values (1,2,5);
insert into assignment values (2,1,5);
insert into assignment values (2,2,5);
insert into assignment values (2,3,10);
SQL> select * from assignment;
EMPNO PROJECT_ID HOURS_WORKED
---------- ---------- ------------
1 1 10
1 2 5
2 1 5
2 2 5
2 3 10
SQL> select empno, count(*) * 500 from assignment
group by empno
having sum(hours_worked) >= 10; 2 3
EMPNO COUNT(*)*500
---------- ------------
1 1000
2 1500
SQL> select empno, count(*) * 500 from assignment
group by empno, project_id
having sum(hours_worked) >= 10; 2 3
EMPNO COUNT(*)*500
---------- ------------
1 500
2 500
Also I think there where condition need to be tweaked. So I think you should be posting your table description, sample data and last but not least follow the forum guidelines.
Regards
Raj
[Updated on: Fri, 02 May 2008 06:24] Report message to a moderator
|
|
|