dhl:不用游标批量更新的SQL语句

在更新一批记录时使用如下语句：

update  publish   set  contentid =
( select   top   1  articles.contentid  from  articles
where  articles.articleID = publish.objectID
)
-- where publish.objectid=@objectID

前提是：publish表的记录不能大于Article的记录，即要插入的目标表中示能插入null,否则会提示错误。

全来没办法，改为游标：

SET  NOCOUNT  ON
DECLARE   @contentID   int
declare   @objectID   int
declare   @countnumber   int
set   @countnumber = 0
DECLARE  publish_cursor  CURSOR   FOR  
     select  a.contentid,a.articleID  from  publish p 
     inner   join  articles a   on  a.articleID = p.objectID
     where  objectid > 0   and  p.contentid <>  a.contentid
     and  (p.cellid = 160   or  cellid = 138 )
     OPEN  publish_cursor

     FETCH   NEXT   FROM  publish_cursor
     INTO   @contentID , @objectID

         WHILE   @@FETCH_STATUS   =   0
         BEGIN
         print   @contentID
         print   @objectID
        
             -- 修改记录
             update  publish  set  ContentID = @contentID   where  objectid = @objectID
             -- 修改结束
             FETCH   NEXT   FROM  publish_cursor  into   @contentID , @objectID
            
         END
     CLOSE  publish_cursor
     DEALLOCATE  publish_cursor
    
GO

select  p.publishid,p.contentid,a.contentid,p.objectID,a.articleID  from  publish p 
inner   join  articles a   on  a.articleID = p.objectID
where  objectid > 0   and  p.contentid <>  a.contentid
and  (p.cellid = 160   or  cellid = 138 )
go

--  update publish set contentid=0 where (cellid=160 or  cellid=138)
--  select * from publish p  where ( p.cellid=160 or  cellid=138)

在没有更好的办法呢？
其实还可以这样：

update  publish   set  contentid =  a.contentid 
from  articles a   inner   join  publish p  on  p.objectID = a.articleID
where  cellid = 138

--  select * from publish where cellid=138
--  update publish set contentid=0 where cellid=138

 

助人等于自助:

tab1  
  -tab2_id  
  -date1  
   
  tab2  
  -tab2_id  
  -data2  
   
   
  现想批量把在tab2中，符合tab2_id=tab1_id的data2更新到tab1当中:

 

update   a   set   data1=b.data2   form   tab1   a,tab2   b   where   a.tab1_id=b.tab2_id

 

SELECT getdate()
--游标变量
declare @pingcoid  varchar(50)
declare @username varchar(50)

declare cur cursor fast_forward for
SELECT pingcoid  FROM [PingCoApp].[AppleGrange].userinfo
open cur
fetch cur into  @pingcoid
while @@fetch_status =0
begin
--循环
SELECT @username=username  FROM pingco.dbo.TUserInfo  where pingcoid = @pingcoid
update [PingCoApp].[AppleGrange].userinfo set username=@username where  pingcoid = @pingcoid
fetch next from cur into @pingcoid
end
close cur
deallocate cur

SELECT getdate()

-------------
SELECT getdate()
update   [PingCoApp].[AppleGrange].userinfo
set  Username = isnull(b.truename,b.username)
--select a.Username , b.truename ,isnull(b.truename,b.username),b.username
from  [PingCoApp].[AppleGrange].userinfo a inner join
      pingco.dbo.TUserinfo b on a.pingcoid = b.pingcoid
SELECT getdate()

转载于:https://www.cnblogs.com/dudu837/archive/2009/04/21/1440480.html