现有表A,结构和数据如下:
userID userDep userNum1 userNum2
1 A 0 0
1 B 0 0
1 C 0 0
2 A 0 0 现通过一个SQL语句查询出如下结果的表
userDep userNum1 userNum2
A 12 6
B 10 7现期望实现如下操作:
将上述结果中的userNum1、userNum2,根据userDep=A表.userDep的原则,更新A表中userID=1的值。
即A表结果为:
userID userDep userNum1 userNum2
1 A 12 6
1 B 10 7
1 C 0 0
2 A 0 0
-----------------------------------------------
谢谢。
userID userDep userNum1 userNum2
1 A 0 0
1 B 0 0
1 C 0 0
2 A 0 0 现通过一个SQL语句查询出如下结果的表
userDep userNum1 userNum2
A 12 6
B 10 7现期望实现如下操作:
将上述结果中的userNum1、userNum2,根据userDep=A表.userDep的原则,更新A表中userID=1的值。
即A表结果为:
userID userDep userNum1 userNum2
1 A 12 6
1 B 10 7
1 C 0 0
2 A 0 0
-----------------------------------------------
谢谢。
解决方案 »
- 问个v$flash_recovery_area_usage的问题
- 字符串缓冲区太小 紧急求助
- 如何找到回滚点?
- 关于Oracle9.0备份
- 求教:如何取近一个自然月平均值的sql的写法
- 我用ODBC开发了一个管理系统,但是如果客户机器没有装ORACLE该怎么操作呢?
- 求助:如何在oracle9i 作一个触发器,功能是在表insert或update的时候,将表的修改以及修改的时间插入到另一个表中
- 一个对大家再也简单不过的问题了!!
- olivertree(自古多情空余恨),beckhambobo(beckham), akinggom(kinggom,onejune4450(中文字符) 进来接分~!
- 回了“已知表名,如何知道该表包含什么字段?sql语句该如何写?”帖子的朋友请进来领分 :)
- 动态SQL方法4,什么意思??
- imp 失败 告急~~~~
set userNum1=B.userNum1,userNum2=B.userNum2
from A
inner join (一个SQL语句查询结果)B on A.userDep=B.userDep
where A.userID=1
set userNum1=B.userNum1,userNum2=B.userNum2
from 表A A
inner join (select userDep,userNum1,userNum2 from 表A where ....)B on A.userDep=B.userDep
where A.userID=1
set A.usernum1=B.num1,A.usernum2=B.num2 where A.usedep=b.userdep
update a set (usernum1,usernum2)=
(select b.usernum1,b.usernum2 from b
where b.userdep=a.userdep)
where exists(select 1 from b where b.userdep=a.userdep) and a.userid=1--result:
1 A 12 6
1 B 10 7
1 C 0 0
2 A 0 0
set userNum1=B.userNum1,userNum2=B.userNum2
from 表A A
inner join (select userDep,userNum1=sum(userNum1),userNum2=sum(userNum2)
from 表A group by userDep)B on A.userDep=B.userDep
where A.userID=1
现有表A,结构和数据如下:
userID userDep userNum1 userNum2
1 A 0 0
1 B 0 0
1 C 0 0
2 A 0 0 现通过一个SQL语句查询出如下结果的表(所查询出的数据来源于其他表,并非表A里的数据)
userDep userNum1 userNum2
A 12 6
B 10 7
set t.usernum1 = (select t2.usernum1
from (select * from test2“查询结果”) t2
where t.userdep = t2.userdep),
t.usernum2 = (select t2.usernum2
from (select * from test2“查询结果”) t2
where t.userdep = t2.userdep)
where t.userid = 1我测过 有个缺陷就是把C的0,0 变成空了“ ”,“ ”
结果
1 A 12 6
1 B 10 7
1 C
2 A 0 0
你看看怎么改一下就好了
看来不能想当然随便加 as啊
set (userNum1, userNum2) =
(select userNum1, userNum2
from B
where B.userDep = A.userDep
)
where userId=1
and
exists
(
select 1
from B
where B.userDep = A.userDep
)如果是10g以上的版本,还可以
merge into A
using (select * from b) B on (A.userDep = B.userDep)
when matched then
update set A.userNum1 = B.userNum1
,A.userNum2 = B.userNum2
where userId = 1;
set userNum1=AA. userNum1 ,
userNum2 = AA. userNum2
from 表A, AA
where AA .userDep=A表.userDep
and A表. userNum1 <>0
AA 是你检索的那个表。 可不可以,
update A a
set (a.userNum1, a.userNum2) = (select b.userNum1, b.userNum2
from (一个sql语句查询的结果)B b
where a.userDep = b.userDep)
where exists (select 1
from (一个sql语句查询的结果)B b
where a.userDep = b.userDep)
and a.userID = 1;
create table TA(userID int,userDep varchar2(4),userNum1 int,userNum2 int)
insert into TA values(1,'A',0,0)
insert into TA values(1,'B',0,0)
insert into TA values(1,'C',0,0)
insert into TA values(2,'A',0,0)
create table TB(userDep varchar2(4),userNum1 int,userNum2 int)
insert into TB values('A',12,6)
insert into TB values('B',10,7)
select * from TA
select * from TB
UPDATE TA set (TA.userNum1,TA.userNum2) =
(select TB.userNum1,TB.userNum2 from TB where TB.userDep =TA.userDep)
where TA.USERID =1
select * from TA