update ly_release set city2 = (select ly_ziliao.city2
from ly_ziliao,ly_release
where ly_ziliao.ziliao_id = ly_release.ziliao_id
group by ly_ziliao.ziliao_id,ly_ziliao.city2)update ly_release set city2 = (select city2
from ly_ziliao
group by ly_ziliao.ziliao_id,city2)
where exists (select null
from ly_ziliao ,ly_release
where ly_ziliao.ziliao_id = ly_release.ziliao_id)
--想要结果 : 如果ly_ziliao,ly_release表两个ziliao_id相同
--那么ly_release表里的 city2属性改成ly_ziliao表里的city2属性
这是生产数据库,不小被我乱了,现在要恢复
from ly_ziliao,ly_release
where ly_ziliao.ziliao_id = ly_release.ziliao_id
group by ly_ziliao.ziliao_id,ly_ziliao.city2)update ly_release set city2 = (select city2
from ly_ziliao
group by ly_ziliao.ziliao_id,city2)
where exists (select null
from ly_ziliao ,ly_release
where ly_ziliao.ziliao_id = ly_release.ziliao_id)
--想要结果 : 如果ly_ziliao,ly_release表两个ziliao_id相同
--那么ly_release表里的 city2属性改成ly_ziliao表里的city2属性
这是生产数据库,不小被我乱了,现在要恢复
解决方案 »
- VC嵌入proc编程访问Oracle数据库问题
- oracle 寻找10g rman备份与恢复电子书
- 疑惑我已经结贴的一个问题
- 通过dblink的查询会占用回滚段吗?
- 改了服务器,就出现ora-12535的错误
- 启动oracle 时,出现ora-01033错误,oracle initialization or shutdown in progress 错误;再用sqlplus 连接时,输入用户名和密码,出现
- 超菜问题:请问oracle里面的tnsnames.ora这个配置文件主要是干什么用的?
- Oracle8i的左连接问题
- oracle不支持rand函数吗?
- sql server用户学oracle应该怎么入手
- 将字符串直接当成NUMBER来比较大小的问题
- 问下 oracle 的查询语句
那么 就把 ly_release的city2设置为4
set city2=city2NEW先检测一下查询语句查出来的结果,是不是你想要改的,然后再运行下面的更新
update ly_release T3 set city2=(
select ly_ziliao.city2 from (ly_ziliao T1 join ly_release T2
on (T1.ziliao_id= T2.ziliao_id))T4
where T3.ziliao_id=T4.ziliao_id
)试试
老大,这里 set city2 = city2NEW 的city2是指ly_release表里的吗,
我不明确啊
(select a.city2
from ly_ziliao a
where a.ziliao_id=ly_release.ziliao_id)
update ly_release set city2 =
(select a.city2
from ly_ziliao a
where a.ziliao_id=ly_release.ziliao_id)
SQL> create table t1(id number,city2 varchar2(10));表已创建。SQL> insert into t1 values(1,'phenix');已创建 1 行。SQL> insert into t1 values(2,'phenix2');已创建 1 行。SQL> insert into t1 values(3,'phenix3');已创建 1 行。SQL> create table t2(id number,city2 varchar2(10));表已创建。SQL> insert into t2(id) values(3);已创建 1 行。SQL> insert into t2(id) values(1);已创建 1 行。SQL> insert into t2(id) values(2);已创建 1 行。SQL> select * from t1; ID CITY2
---------- ----------
1 phenix
2 phenix2
3 phenix3SQL> select * from t2; ID CITY2
---------- ----------
3
1
2SQL> ed
已写入文件 afiedt.buf 1 update t2 tName3 set city2=(
2 select tName1.city2 from t1 tName1 join t2 tName2 on(tName1.id=tName2.id
)
3* where tName3.id=tName1.id)
SQL> /已更新3行。SQL> select * from t2; ID CITY2
---------- ----------
3 phenix3
1 phenix
2 phenix2SQL> select * from t1; ID CITY2
---------- ----------
1 phenix
2 phenix2
3 phenix3SQL>