update t1
set (Col1) = ( select Col1 from t2
where t1.Col_Primary = t2.Col_Primary );
这样把t1中有的,t2中没有的记录的Col1设成 NULL了?
set (Col1) = ( select Col1 from t2
where t1.Col_Primary = t2.Col_Primary );
这样把t1中有的,t2中没有的记录的Col1设成 NULL了?
解决方案 »
- 关于oracle只读表空间问题请教
- 命令startup和“C:>sqlplus system/password@orcl”都是启动数据库吗?
- [NS-35206] 目录已在使用 与 [NS-20802]Oracle Net Configuration Assistant失败
- 求OSB客户端实例
- 请问:执行DDL语句时,回滚段和临时段,回滚表空间和临时表空间,都起什么样的作用?在什么时候起作用?
- 查看当前操作消耗资源的情况
- oracle中百分比问题
- 求助!清空数据库
- SQL语句行转为列?
- Oracle 9i插入数据出错(ORA-01653),请大家帮忙?
- Oracle中如何选出表的前几行?
- ora-28009:connection as sys should be as sysdba or sysoper 错误,昨天还是可以的呢
---------- ------------------------------
1 A
2 B
3 CExecuted in 0.157 secondsSQL> SELECT * FROM T2; COL1 CNAME
---------- ------------------------------
2 B
3 c
4 dExecuted in 0.14 seconds
SQL> update t1
2 set (CNAME) = ( select CNAME from t2
3 where t1.COL1 = t2.COL1);3 rows updatedExecuted in 0.141 secondsSQL> SELECT * FROM T1; COL1 CNAME
---------- ------------------------------
1
2 B
3 cExecuted in 0.156 secondsSQL> update t1
2 set (CNAME) = ( select CNAME from t2
3 where t2.COL1 = t1.COL1)
4 WHERE
5 EXISTS(
6 SELECT 1 FROM T2 WHERE t1.COL1 = t2.COL1
7 ) ;2 rows updatedExecuted in 0.172 secondsSQL> SELECT * FROM T1; COL1 CNAME
---------- ------------------------------
1 A
2 B
3 cExecuted in 0.171 seconds
这样真是很麻烦,期待高人指点
update很花时间
2 set (CNAME) = NVL(( select CNAME from t2
3 where t1.COL1 = t2.COL1),CNAME)
4 /3 rows updatedSQL> SELECT * FROM T1;COL1 CNAME
---- -----
3 c
2 B
1 A这样行吗??
不用exists
如果t2.Col_Primary 在t2中有唯一约束的话可以这样写
update
(
select t1.coll ,t2.coll coll2
from t1,t2
where t1.Col_Primary=t2.Col_Primary
)
set coll=coll2