CODEWITHSUNDEEP
mysqlsqloracle-database
Ravi.
Ravi.

Reputation: 674

Update statement returning single-row subquery returns more than one row

I'm new to SQL and I'm stuck with a query.

I have 3 tables employees, departments and salary_paid. I'm trying to update bonus column in salary_paid table by giving this condition

give 10% bonus on total salary to the employees who are not in IT departments.

I came up with this query 

update salary_paid 
set bonus=(select (0.1*total_salary) "Bonus" 
           from salary_paid, departments, employees
           where 
               employees.department_id=departments.department_id and 
               employees.employee_id=salary_paid.employee_id and
               departments.department_name!='IT')
           ;

However it returns this error

ORA-01427: single-row subquery returns more than one row

I'm completely clueless on this, please help. Thanks in advance

Upvotes: 0

Views: 16699

Answers (5)

Aun Raza
Aun Raza

Reputation: 1

i wanted to update emails in hr user and had a same problem then i came accorose this which worked for me

update (select email as oldemail, substr(first_name,1,1)||''||last_name||''||'@gmail.com' as email from employees
inner join departments
on
employees.department_id= departments.department_id
)t
set t.oldemail=t.email

Upvotes: 0

Ganesh Jadhav
Ganesh Jadhav

Reputation: 2848

Try this:

UPDATE
    (
        SELECT *
        FROM employees e LEFT JOIN salary_paid sp ON e.employee_id = sp.employee_id
            LEFT JOIN departments d ON d.department_id = e.department_id
    ) t
SET t.bonus = 0.1 * t.total_salary
WHERE t.department_name != 'IT';

You query was updating all the rows in the table with the result of the sub-query. Also, the sub-query was returning more than one rows. When setting a value, the sub-query should always return single row with single column.
In Oracle, these problems are solved by using join, as shown above. This will update the bonus column using values from the respective total_salary columns. No need to use sub-query.

Upvotes: 1

Ronald Alexander Kailola
Ronald Alexander Kailola

Reputation: 149

try this

update salary_paid a,departments b, employees c set a.bonus=(0.1*a.total_salary) "Bonus" 
where c.department_id=b.department_id and c.employee_id=a.employee_id and b.department_name!='IT';

CMIIW

Upvotes: 0

Mitul Shah
Mitul Shah

Reputation: 1566

subquery should always return single row in return. but you are getting here multiple row using select query. so first check your select query.

select (0.1*total_salary) "Bonus" from salary_paid, departments, employees
where employees.department_id=departments.department_id and employees.employee_id=salary_paid.employee_id and departments.department_name!='IT'

This query should have only one result but u are trying to getting multiple rows.

let's try with putting LIMIT in your select query

select (0.1*total_salary) "Bonus" from salary_paid, departments, employees
where employees.department_id=departments.department_id and employees.employee_id=salary_paid.employee_id and departments.department_name!='IT' limit 0,1

you need to change your select query in that way you get single row.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Your inner query (select (0.1*total_salary) "Bonus" from salary_paid is returning more than one value and so can't be assigned to bounus column.

Instead try updating using Joins like this

   UPDATE 
    (SELECT salary_paid.bonus as oldBonus, 0.1*salary_paid.total_salary as newBounus
     FROM salary_paid
     INNER JOIN employees
     ON salary_paid.employee_id = employees.employee_id
     INNER JOIN departments
     ON departments.department_id = employees.department_id 
     WHERE departments.department_name != 'IT'
    ) t
    SET t.oldBonus= t.newBounus

Upvotes: 2

Related Questions