update topic set lastpostime=(select max(dateandtime) from bbs1 where bbs1.rootid=topic.topicid)
update topic set lastposttime =(select max(dateandtime) from bbs1 where rootid=topic.topicid)or update a set a.lastposttime = b.dateandtime from topic a join (select rootid,max(dateandtime) as dateandtime from bbs1 group by bbs1) b on a.topicid = b.rootid
用这个更新:update topic set lastposttime=b.dd from topic a inner join (select rootid,max(dateandtime) as dd from bbs1 group by rootid) b on a.topicid=b.rootid
下面是我的数据测试--创建数据测试环境: declare @bbs1 table(dateandtime datetime,rootid int) insert into @bbs1 select '2003-7-31 16:18',1106 union all select '2003-7-31 16:15',1106 union all select '2002-2-14 21:30',5 union all select '2002-2-14 11:30',5 union all select '2002-1-12 12:30', 5declare @topic table(topicid int,lastposttime datetime) insert into @topic select 1106,null union all select 5,null--更新数据 update @topic set lastposttime=b.dd from @topic a inner join (select rootid,max(dateandtime) as dd from @bbs1 group by rootid) b on a.topicid=b.rootid--显示更新后的结果 select * from @topic
update a set a.lastposttime = b.dateandtime
from topic a
join (select rootid,max(dateandtime) as dateandtime from bbs1 group by bbs1) b
on a.topicid = b.rootid
from topic a inner join
(select rootid,max(dateandtime) as dd from bbs1 group by rootid) b
on a.topicid=b.rootid
declare @bbs1 table(dateandtime datetime,rootid int)
insert into @bbs1
select '2003-7-31 16:18',1106
union all select '2003-7-31 16:15',1106
union all select '2002-2-14 21:30',5
union all select '2002-2-14 11:30',5
union all select '2002-1-12 12:30', 5declare @topic table(topicid int,lastposttime datetime)
insert into @topic
select 1106,null
union all select 5,null--更新数据
update @topic set lastposttime=b.dd
from @topic a inner join
(select rootid,max(dateandtime) as dd from @bbs1 group by rootid) b
on a.topicid=b.rootid--显示更新后的结果
select * from @topic
----------- ------------------------------------------------------
1106 2003-07-31 16:18:00.000
5 2002-02-14 21:30:00.000
insert into topic(lastpostime) select max(dateandtime) from bbs1,topic where bbs1.rootid=topic.topicid)
set lastposttime=(select max(dateandtime)
from bbs1
where rootid = topic.topicid)
1 dateandtime值为空
2 topic表中无对应的rootid,这时update可能就不行了,用insert才行