更新失败，因为子查询返回超过 1 个值 [英] 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

推荐答案

触发器是按 statement 执行的，而不是按 row 执行的，这是错误的根源.您的触发器假定插入和删除的表只会有一行，但这是完全错误的.
插入/删除表中的行数是受 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.

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