17,086
社区成员
发帖
与我相关
我的任务
分享
update table01
set group=(select distinct group from table02 where table02.time=table01.time)
where exists(select 1 from table02 where table02.time=table01.time)
scott@YPCOST> create table dept2 as select * from dept;
Table created.
scott@YPCOST> insert into dept2 values(50,'develop','houston');
1 row created.
scott@YPCOST> commit;
Commit complete.
scott@YPCOST> select * from dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 develop houston
scott@YPCOST> update dept2 set loc=(select loc from dept where dept.deptno=dept2.deptno)
2 /
5 rows updated.
scott@YPCOST> select * from dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 develop
scott@YPCOST> rollback;
Rollback complete.
scott@YPCOST> update dept2 set loc=(select loc from dept where dept.deptno=dept2.deptno)
2 where exists(select 1 from dept where dept.deptno=dept2.deptno);
4 rows updated.
scott@YPCOST> select * from dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 develop houston
update table01 t1
set t1.group=(select t2.group from table02 t2 where t2.time=t1.time)
update table01 set group=(select group from table02 where table02.time=table01.time)
where exists(select 1 from table02 where table02.time=table01.time)
update table01 t1
set t1.group=(select t2.group from table02 t2 where t2.time=t1.time)
where exists(select 1 from table02 t3 where t3.time=t1.time);