更新失败，因为子查询返回了多个值 [英] Update fails because Subquery returned more than 1 value

问题描述

尽管没有任何子查询，但我尝试更新表时遇到以下错误:

I Get the following error when i try to update my table although there's n't any sub query :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 

---

我的查询:

---

MY QUERY :

UPDATE  t1 
SET t1.modified = 2
FROM TransActions AS t1
INNER JOIN Ruser R
ON t1.USERID = r.USERID
WHERE  r.dep_code = 54 and r.dep_year =2014
and YEAR(t1.checktime) =2016 and MONTH(t1.checktime) =1 and  t1.modified   = 0

---

选择的数据如下:

---

The data selected like this :

USERID  empNum
3090    25
3090    25
2074    464

---

根据评论，我会触发更新:

---

According to the comments my update trigger :

after update 
as

declare @userid int , @date date 


if (select userid from inserted)<>(select userid from deleted )
raiserror ('YOU ARE NOT ALLOWED TO PERFORME THIS ACTION',10 , 1)
ELSE
begin 
    set nocount on;

    set @userid = (select userid from inserted)
    set @date = (select convert(date , checktime) from inserted)


    exec calc_atten @date , @userid 
end

推荐答案

触发器是根据声明执行的，而不是根据行执行的，这就是错误的根源.您的触发器假定插入和删除的表将仅具有一行，但是那完全是错误的.
插入/删除表中的行数是DML语句(更新/插入/删除)影响的行数.

Triggers are executed per statement, not per row, that's the source of your error. Your trigger assumes that the inserted and deleted tables will only ever have one row, however that is simply wrong.
The number of rows in the inserted / deleted tables is the number of rows effected by the DML statement (update/insert/delete).

我不知道过程 calc_atten 做什么，但是您需要找到一种在设定级别而不是像现在那样在标量变量上执行它的逻辑的方法.

I don't know what the procedure calc_atten does, but you need to find a way to execute it's logic on a set level and not on scalar variables as it does now.

应更改触发器开头的条件以适合多行更新.一种实现方法是:(如果我知道表的结构，我可能会写得更短更好)

Your condition at the beginning of the trigger should be changed to fit a multi-row update. One way to do it is this: (I could probably write it shorter and better if I would have known the table's structure)

IF EXISTS (
    SELECT 1
    FROM deleted d 
    INNER JOIN inserted i
    ON d.[unique row identifier] = i.[unique row identifier]
    WHERE i.userId <> d.UserId
)

* [唯一行标识符]代表该表中每行唯一的任何列或列组合.如果唯一的行标识符包含UserId列，则它将无法正常工作.

*[unique row identifier] stands for any column or column combination that is unique per row in that table. If the unique row identifier contains the UserId column then this will not work properly.

这篇关于更新失败，因为子查询返回了多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！