在更新一批記錄時使用如下語句:
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