有两张表表一:SAVEBAL 表二:CROPSUB
ACCNO SUBNO ACCNO CNTYPE
668503759 212341 668503759 1
192189521 235111 192189521 1
192325079 123672 192371630 1
192371630 235444 279293591 1
279293591 123777 279293591 1
279349348 198121 279349348 1
279394683 235081 279394683 1
表一和表二的ACCNO是关联的.
我现在想把表一里SUBNO的值以 "235"开头的 ACCNO对应在表二里的CNTYPE值 由1 更新为2即写一个 update语句 使表二变成:表二:CROPSUB
ACCNO CNTYPE
668503759 1
192189521 2
192371630 1
279293591 2
279293591 1
279349348 1
279394683 2
请高手帮忙写一个update语句,谢谢.
ACCNO SUBNO ACCNO CNTYPE
668503759 212341 668503759 1
192189521 235111 192189521 1
192325079 123672 192371630 1
192371630 235444 279293591 1
279293591 123777 279293591 1
279349348 198121 279349348 1
279394683 235081 279394683 1
表一和表二的ACCNO是关联的.
我现在想把表一里SUBNO的值以 "235"开头的 ACCNO对应在表二里的CNTYPE值 由1 更新为2即写一个 update语句 使表二变成:表二:CROPSUB
ACCNO CNTYPE
668503759 1
192189521 2
192371630 1
279293591 2
279293591 1
279349348 1
279394683 2
请高手帮忙写一个update语句,谢谢.
解决方案 »
- oracle安装失败,以前有安过,卸载后重新安装也没有问题,上次我重安前把注册表什么的都删除了再安就不行了
- sqlplus 怎样看用户拥有的表?(记得有个视图显示啊)
- 关于Oracle10G数据导入至另一个用户名下
- oracle时间比较的问题
- 请帮忙查找Update此语句的错误
- 帮我看一条sql语句,想不通为什么会有这样的作用
- 一个关于JAVA取ORACLE数据库中存储过程信息的问题
- 关于数据库效率问题,大家进来聊聊吧
- oracle9i的OMS提交任务以后,半天都没有反映是怎么一会事情呢?
- ORA-12154: TNS:could not resolve the connect identifier specified
- RMAN的问题,哪为大侠帮忙看一下哦
- Oracle union效率
create table savebal(accno int,sumno int);
insert into savebal select 668503759, 212341 from dual;
insert into savebal select 192189521, 235111 from dual;
insert into savebal select 192325079, 123672 from dual;
insert into savebal select 192371630, 235444 from dual;
insert into savebal select 279293591, 123777 from dual;
insert into savebal select 279349348, 198121 from dual;
insert into savebal select 279394683, 235081 from dual;create table cropsub(accno int,cntype int);
insert into cropsub select 668503759, 1 from dual;
insert into cropsub select 192189521, 1 from dual;
insert into cropsub select 192371630, 1 from dual;
insert into cropsub select 279293591, 1 from dual;
insert into cropsub select 279293591, 1 from dual;
insert into cropsub select 279349348, 1 from dual;
insert into cropsub select 279394683, 1 from dual;update cropsub a set a.cntype=2
where exists(select 1 from savebal b where a.accno=b.accno and substr(b.sumno,1,3)=235);select * from cropsub;
create table savebal(accno int,sumno int);
insert into savebal select 668503759, 212341 from dual;
insert into savebal select 192189521, 235111 from dual;
insert into savebal select 192325079, 123672 from dual;
insert into savebal select 192371630, 235444 from dual;
insert into savebal select 279293591, 123777 from dual;
insert into savebal select 279349348, 198121 from dual;
insert into savebal select 279394683, 235081 from dual;create table cropsub(accno int,cntype int);
insert into cropsub select 668503759, 1 from dual;
insert into cropsub select 192189521, 1 from dual;
insert into cropsub select 192371630, 1 from dual;
insert into cropsub select 279293591, 1 from dual;
insert into cropsub select 279293591, 1 from dual;
insert into cropsub select 279349348, 1 from dual;
insert into cropsub select 279394683, 1 from dual;
merge into cropsub a
using savebal b
on (a.accno=b.accno and substr(b.sumno,1,3)=235)
when matched then update set a.cntype=2
select * from cropsub;
-----------
668503759 1
192189521 2
192371630 2
279293591 1
279293591 1
279349348 1
279394683 2
-------------
to_char(b.subno) like '235%'可以改成substr(b.subno,1,3)='235'
若SUBNO字段是字符型的话,写成b.subno>='235' and b.subno<'236'
效率应该更高
楼主熟悉下exists的用法,其实不难的
exists 恩 学习了 我都忘了有这关键字了
这么写对吗?