with as 更新表无效

[班长老六哥]

我想把每个表的第一笔数据的一个值存到另一个表里，更新第一个好使，第二个就不好使了，求高手帮忙

with t1 as
(
SELECT TOP 1 TAV
  FROM Tin_FengXiangYuan order by 时间 desc
  
 ),
 
 t2 as
(
SELECT TOP 1 TAV
  FROM Tin_YiPinYuan order by 时间 desc
  
 )
  update ATable_Output  set TinAV= t1.TAV from t1 where ATable_Output.StationID=0
  update ATable_Output  set TinAV= t2.TAV from t2 where ATable_Output.StationID=1

[Tiger_Zhao]

两个更新语句要用两个With

with t1 as
(
SELECT TOP 1 TAV
  FROM Tin_FengXiangYuan order by 时间 desc
  
 ),
  update ATable_Output  set TinAV= t1.TAV from t1 where ATable_Output.StationID=0
 
;with t2 as
(
SELECT TOP 1 TAV
  FROM Tin_YiPinYuan order by 时间 desc
  
 )
  update ATable_Output  set TinAV= t2.TAV from t2 where ATable_Output.StationID=1

[班长老六哥]

7楼和5楼都可以实现，谁的方法和效率更高呢

[aoye6202]

引用 1 楼 aoye6202 的回复:

with 后的SQL语句只能有一句， 使用过一次后 with 将失效

http://blog.csdn.net/sprita1/article/details/16857111 看看这个，可以结贴了

[aoye6202]

with 后的SQL语句只能有一句， 使用过一次后 with 将失效

[班长老六哥]

那我想实现把每个表的查询结果更新的新表里怎么做呢

[tcmakebest]

with其实只是一条完整语句的一部分, 并不是单独存在的, 所以第二个update跟第一个with毫无关系.

[道玄希言]

引用 3 楼 zk12668 的回复:

那我想实现把每个表的查询结果更新的新表里怎么做呢

这么写可以不？ update ATable_Output set TinAV= t1.TAV from ( SELECT TOP 1 TAV FROM Tin_FengXiangYuan order by 时间 desc) t1 where ATable_Output.StationID=0 update ATable_Output set TinAV= t2.TAV from (SELECT TOP 1 TAV FROM Tin_YiPinYuan order by 时间 desc) t2 where ATable_Output.StationID=1

[comma212164]

update test1 set test1.shl=t2.shl from 
      (select test2.shl,test2.id from test2,test1 where test2.id=test1.id)t2 
   where test1.id=t2.id 

给你参考