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

 

 

 

免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。