update的关联表批量更新

update的关联表批量更新2011-08-19 博客园邀月在更新一批记录时使用如下语句：

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