update的关联表批量更新


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

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



相关阅读:
Unicode与ISO10646(上)
使用Linux的rsync命令实现:多服务器镜像同步
FreeBSD如何保存和使用内核更改信息
Windows Server 2008下调教IE有巧招上
dedecms V5.6 文档关键词自动链接解决方案
模拟SQLSERVER的两个函数:dateadd(),datediff()
PHP应用程序加速探索之简介
CSS翻页效果,鼠标放上时变化,无需JS
用js互相调用iframe页面内的js函数
Exchange 2000 Instant Messaging(组图)
FreeBSD为powerd设置cpu最小工作频率
Mysql LONGTEXT 类型存储大文件(二进制也可以) (修改+调试+整理)
Wordpress技巧:解决注册登录死循环问题
Exadel宣布AJAX在Studio Pro 3.5中支持JSF
快速导航

Copyright © 2016 phpStudy | 皖ICP备18014864号-4