表table01
time group
20110310
20110311
20110312
20110313
20110314
...表 table02
time group
20110310 1
20110311 2
20110312 2
20110313 3
20110314 4
... ...
现在想根据table02的group来更新table01的group,用time做关联,
不知道怎么写啊
time group
20110310
20110311
20110312
20110313
20110314
...表 table02
time group
20110310 1
20110311 2
20110312 2
20110313 3
20110314 4
... ...
现在想根据table02的group来更新table01的group,用time做关联,
不知道怎么写啊
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);
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)
呵呵
不过我弄错了,
两个表应该是这样的
表table01
time group
20110310
20110310
20110311
20110313
20110313
20110314
...表 table02
time group
20110310 1
20110310 2
20110311 2
20110313 3
20110313 3
20110314 4
... ...
就是说time有重复的值
time group
20110310
20110310
20110311
20110313
20110313
20110314
...表 table02
time group
20110310 1
20110310 1
20110311 2
20110313 3
20110313 3
20110314 4
... ...
-- 还是 group的总和?
如果table02中没有而table01有的time 那行的group就会被更新为nullscott@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 houstonscott@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 developscott@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
比如time是20110310 那group就是1,
table02 update时也是1
where exists(select 1 from table02 where table02.time=table01.time)
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)