Home » Other » Training & Certification » UPDATE sql statement help please
UPDATE sql statement help please [message #317637] Fri, 02 May 2008 04:54 Go to next message
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 #317641 is a reply to message #317637] Fri, 02 May 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Expert notion is defined in the sticky: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: UPDATE sql statement help please [message #317662 is a reply to message #317637] Fri, 02 May 2008 06:07 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: grouping the records problem help please
Next Topic: URGENT: Calculation query
Goto Forum:
  


Current Time: Thu Mar 28 15:24:29 CDT 2024