通过select * from TimeRecords a where a.Sign_DateTime =(select min(b.Sign_DateTime) from TimeRecords b
where a.Card_ID=b.Card_ID and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)<=0 and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)>=-5)查询到满足参数要求的记录,需要对这些记录的一个字段更新(Records_State 这是为2)如何实现?
where a.Card_ID=b.Card_ID and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)<=0 and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)>=-5)查询到满足参数要求的记录,需要对这些记录的一个字段更新(Records_State 这是为2)如何实现?
你的方法只是显示的为Records_State=2 并没有真正更新表内字段值
update timerecords set Records_State=2
where 主键 in( select 主键 from TimeRecords a where a.Sign_DateTime =(select min(b.Sign_DateTime) from TimeRecords b
where a.Card_ID=b.Card_ID and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)<=0 and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)>=-5)
)
update TimeRecords set Records_State=2
from (select * from TimeRecords a where a.Sign_DateTime =(select min(b.Sign_DateTime) from TimeRecords b
where a.Card_ID=b.Card_ID and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)<=0 and DateDiff(Mi,a.Sign_DateTime,b.Sign_DateTime)>=-5)
) b where TimeRecords.Card_ID=b.Card_ID