现有表A,结构和数据如下:
userID userDep userNum
1 A 0
1 B 0
1 C 0
2 A 0 现通过一个SQL语句查询出如下结果的表
userDep userNum
A 12
B 10
备注:产生以上表的SQL中,使用了sum()聚合函数 现期望实现如下操作:
将上述结果集中的userNum,根据userDep=A表.userDep的原则,更新A表中userID=1的userNum值。
即A表结果为:
userID userDep userNum
1 A 12
1 B 10
1 C 0
2 A 0
-----------------------------------------------
有朋友建议如下SQL语句:update @t
set usernum = s.usernum
from @t t inner join @s s
on t.userid = 1 and t.userdep = s.userdep
select * from @t但提示:ORA-00933:SQL命令未正确结束。请问该怎么处理,谢谢原帖见:
http://topic.csdn.net/u/20081030/21/8755ca69-060c-4db7-a637-05a54a16f151.html?seed=1159495547
userID userDep userNum
1 A 0
1 B 0
1 C 0
2 A 0 现通过一个SQL语句查询出如下结果的表
userDep userNum
A 12
B 10
备注:产生以上表的SQL中,使用了sum()聚合函数 现期望实现如下操作:
将上述结果集中的userNum,根据userDep=A表.userDep的原则,更新A表中userID=1的userNum值。
即A表结果为:
userID userDep userNum
1 A 12
1 B 10
1 C 0
2 A 0
-----------------------------------------------
有朋友建议如下SQL语句:update @t
set usernum = s.usernum
from @t t inner join @s s
on t.userid = 1 and t.userdep = s.userdep
select * from @t但提示:ORA-00933:SQL命令未正确结束。请问该怎么处理,谢谢原帖见:
http://topic.csdn.net/u/20081030/21/8755ca69-060c-4db7-a637-05a54a16f151.html?seed=1159495547
updtae 表名
set 字段=
where 条件其中SET字段值可以用子查询获得,但必须一行纪录只能有获得一个更新值
而where 条件也可以用子查询实现
userDep userNum
A 12
B 10
你这段sql是怎么产生的?
A 12
B 10
假设你这个已经是张表Bupdate a
set a.userNum =
(
select nvl(b.userNum,0)
from b
where a.userDep=b.userDep (+))不过你这个结果集有问题啊
按你的要求
userID userDep userNum
1 A 12
1 B 10
1 C 0
2 A 0
而USERID没有做关联
from tblt1 a
left join tblt2 b on a.userID=b.userID
left join tblt3 c on a.DepID =c.DepID
group by c.userDep
类似上面的带聚合函数的SQL.
因为不知道下面结果集是怎么出来的:
userDep userNum
A 12
B 10
SET a.usernum = (SELECT NVL (b.usernum, 0)
FROM (SELECT c.userdep, COUNT (b.usernum) usernum
FROM tblt1 a LEFT JOIN tblt2 b
ON a.userid = b.userid
LEFT JOIN tblt3 c ON a.depid = c.depid
GROUP BY c.userdep) b
WHERE a.userdep = b.userdep(+))还是上面的问题按你给的结果集这个实际上是有问题的,userid没做关联,也意义是dept a的都会是12
UPDATE (SELECT USERNUM, COUNTS
FROM A,
(SELECT C.USERDEP USERDEP, COUNT(B.USERNUM) COUNTS
FROM TBLT1 A
LEFT JOIN TBLT2 B ON A.USERID = B.USERID
LEFT JOIN TBLT3 C ON A.DEPID = C.DEPID
GROUP BY C.USERDEP) B
WHERE A.USERDEP = B.USERDEP
AND A.USERID = 1)
SET USERNUM = COUNTS;
请教下,上面语句中的(+)是什么意思呢,谢谢。
update 表A set userNum=(我查询出的结果中的userNum值,且A表的userDep=查询出的结果中的userDep)
where USERID=1