想根据一个结果集来更新表中的字段,也就是这个意思:
update a set a.s=b.ss,a.f=b.ff where id in (select id from b where xxx)
我的第一想法是使用游标。结果发现游标的效率太低了。经高人指点使用了临时表
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
declare
@t
table
(userid
int
,username
nvarchar
(
50
),commenttime
datetime
,questionid
nvarchar
(
50
))
2
3 insert @t (userid,username,commenttime,questionid)
4 select userid,username,commenttime,questionid from comment c
5 where questionid in (
6 select questionId from question
7 where CreateTime < DATEADD (minute, 1440 , getdate ())
8 and IsAutoAdd = 100 and questionstatus <> 1 )
9 and not exists
10 ( select 1 from comment where questionid = c.questionid and commenttime > c.commenttime);
11
12
13 update question set lastcommentuserID = t.userid,
14 lastcommentuserName = t.username,
15 LastCommentTime = t.commenttime
16 from @t t
17 where question.questionID =t.questionid
2
3 insert @t (userid,username,commenttime,questionid)
4 select userid,username,commenttime,questionid from comment c
5 where questionid in (
6 select questionId from question
7 where CreateTime < DATEADD (minute, 1440 , getdate ())
8 and IsAutoAdd = 100 and questionstatus <> 1 )
9 and not exists
10 ( select 1 from comment where questionid = c.questionid and commenttime > c.commenttime);
11
12
13 update question set lastcommentuserID = t.userid,
14 lastcommentuserName = t.username,
15 LastCommentTime = t.commenttime
16 from @t t
17 where question.questionID =t.questionid
18 --set @error=@error+@@error;