由于返回多行而导致子查询更新失败

Question

我试过了:

update iclock_transaction set inout=(select(b.rno%2)  from (
select emp_code,Row_number() over( Partition by emp_code,convert (date,punch_time) order by punch_time )as rno from iclock_transaction)b)

我得到了以下错误:

Msg 512, Level 16, State 1, Line 26
子查询返回多个值。当子查询紧跟在=、!=、<、<=、>、>=之后，或者当子查询用作表达式时，不允许这样做。语句已被终止。

Answer 1

您甚至不需要子查询，您可以直接更新派生表。

看起来你真的需要这个

update ict
set inout = ict.rno % 2
from (
select
ict.*,
Row_number() over (Partition by ict.emp_code, convert(date, ict.punch_time) order by ict.punch_time ) as rno
from iclock_transaction ict
) ict;