用临时表代替游标实现多条数据的动态更新

 想根据一个结果集来更新表中的字段，也就是这个意思：

update a set a.s=b.ss,a.f=b.ff where id in (select id from b where xxx)

我的第一想法是使用游标。结果发现游标的效率太低了。经高人指点使用了临时表

代码：

View Code

 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

18 --set @error=@error+@@error; 

转载于:https://www.cnblogs.com/dzxw2371/archive/2011/07/29/2121027.html