oracle 返回单行子查询的更新语句返回多行

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

提问by Ravi.

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

我是 SQL 的新手，并且遇到了查询。

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

我有 3 个表员工、部门和工资支付。我正在尝试通过提供此条件来更新salary_paid 表中的奖金列

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

ORA-01427: 单行子查询返回多于一行

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

我对这个完全一窍不通，请帮忙。提前致谢

回答by Mudassir Hasan

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

您的内部查询(select (0.1*total_salary) "Bonus" from salary_paid返回多个值，因此无法分配给 bounus 列。

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

回答by Ganesh Jadhav

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 bonuscolumn using values from the respective total_salarycolumns. No need to use sub-query.

您的查询正在使用子查询的结果更新表中的所有行。此外，子查询返回不止一行。设置值时，子查询应始终返回单行单列。
在 Oracle 中，这些问题都是通过使用 join 来解决的，如上图所示。这将bonus使用来自相应total_salary列的值更新该列。无需使用子查询。

回答by Mitul Shah

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 LIMITin your select query

让我们尝试将LIMIT放在您的选择查询中

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.

您需要以获取单行的方式更改选择查询。

回答by Ronald Alexander Kailola

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

CMIIW

回答by Aun Raza

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

我想在 hr 用户中更新电子邮件并且遇到了同样的问题然后我来了这对我有用

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