关联表的批量更新(downmoon)

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

update publish set contentid =
( select top 1 articles.contentid from articles
where articles.articleID = publish.objectID
)
-- wherepublish.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 publishp
inner join articlesa 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 publishp
inner join articlesa on a.articleID = p.objectID
where objectid > 0 and p.contentid <> a.contentid
and (p.cellid = 160 or cellid = 138 )
go

-- updatepublishsetcontentid=0where(cellid=160orcellid=138)
-- select*frompublishpwhere(p.cellid=160orcellid=138)

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

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

-- select*frompublishwherecellid=138
-- updatepublishsetcontentid=0wherecellid=138

邀月注：本文版权由邀月 和优快云共同所有，转载请注明出处。
助人等于自助! 3w@live.cn