作者dividi (阿福加油~!!)
看板Database
标题[SQL ] set + group by + inner join一起用
时间Thu Sep 8 07:19:09 2011
我使用的是MS SQL
有个表格二如下:
id PP1Runs
AA 10
BB 20
CC 30
AA 40
我想把表格二各ID的总合更新到表格一去
但是使用下面的code一直有错误
update Table1
set Bat_PP1runs = t2.SUM(PP1RUNS)
from Table t1 inner join Table2 t2
on t1.ID = t2.ID
group by ID
Incorrect syntax near the keyword 'group'.
奇怪的是如果我单纯用select而不是set却没问题
变成我现在是把他先select into第三个表格再更新回第二个表格
select ID as ID, sum(PP1Runs) as PP1Runs into #temp
from Table1 t1 inner join Table2 t2
on t1.ID = t2.ID
group by ID
update Table1
set Bat_PP1runs = t3.PP1Runs
from Table t1 inner join #temp t3
on t1.ID = t2.ID
怎麽看都是多此一举...
好像只需要更动一点部分但我一直试不出来
请强者指点一下orz
非常感谢
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 77.99.129.48