两个表联合查询出来的东西,怎样更新回其中的一个表?
比如说下面的语句:
SELECT table1.word , SUM(table2.Pxx*table2.Pyx) AS Expr1
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
现在想把这个结果放回table1对应的字段sums处,即set sums=expr1
具体的语句该怎么加进去?
比如说下面的语句:
SELECT table1.word , SUM(table2.Pxx*table2.Pyx) AS Expr1
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
现在想把这个结果放回table1对应的字段sums处,即set sums=expr1
具体的语句该怎么加进去?
解决方案 »
- SetForegroundWindow后窗口是否重绘完成
- 编辑器:如何找到toolbar structure具体的图标呢,不知道是放在哪里,想加入一个“H”图标表示字符类型转换。。。请指教
- ZwQueryInformationThread API菜鸟一问~
- 程序在WinXP下界面最大化问题?
- 【有关ADO操作的问题】“找不到可安装的ISAM”
- DxDBGrid的列宽可以固定吗?
- 去看看吧,小心你的e@mail被你的老板監視哦!!
- 急求一代理服务器的IP地址,通过马上给分!拜托了各位。。。。:)
- 重启其他进程(哪位大侠解释一下),多谢
- 可否分割TDBNavigator组件的按钮面板
- 怎么在我2000se+delphi7下面写的一个屏保程序在别的机器上都不能运行????
- 50分!谁能帮我看看这个简单的aql语句有什么问题?(各位大虾帮帮小妹吧!!!在线等待)
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
)
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
)
update table1 set sums=(SELECT table1.word , SUM(table2.Pxx*table2.Pyx) AS Expr1
FROM ……
报错说:type mismatch in expression
报错又说:single row subquery produced more than one row
update table1 set sums=(SELECT SUM(table2.Pxx*table2.Pyx) AS Expr1
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
)
FROM table1 INNER JOIN table2
ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
)
go
insert table1 values('aaa',80)
insert table1 values('bbb',86)
insert table1 values('ccc',75)
create table table2(s1 char(10),s2 char(10),pxx float,pyx float)
go
insert table2 values('ddd','aaa',3,4)
insert table2 values('aaa','ccc',1,2)
--以下为更新语句
update table1
set sums=(
SELECT SUM(table2.Pxx*table2.Pyx)
FROM table1 INNER JOIN table2 ON table1.word = table2.S1 OR table1.word = table2.S2
GROUP BY table1.word
having table1.word=a.word)
from table1 a drop table table1
drop table table
--测试结果
--word sums
--aaa 14
--bbb null
--ccc 2
操作系统win2000,数据库sql server2000下测试成功
SET sums=(select sums from table2
WHERE table1.s1 = table2.Ss)