select * from test_1; ID VAL
---------- ----------
1 1
2 2
3 3
4 4
5 5 select * from test_2; NO VAL
---------- ----------
1 0
2 0
3 0
4 0
5 0现在要根据id=no更新test_2表中的val字段为test_1表的val字段值,要求用update,要高效率附录建表语句:
create table test_2(no number,val number)
insert into test_2 values(1,0);
insert into test_2 values(2,0);
insert into test_2 values(3,0);
insert into test_2 values(4,0);
insert into test_2 values(5,0);create table test_1(id number,val number)
insert into test_1 values(1,1);
insert into test_1 values(2,2);
insert into test_1 values(3,3);
insert into test_1 values(4,4);
insert into test_1 values(5,5);
---------- ----------
1 1
2 2
3 3
4 4
5 5 select * from test_2; NO VAL
---------- ----------
1 0
2 0
3 0
4 0
5 0现在要根据id=no更新test_2表中的val字段为test_1表的val字段值,要求用update,要高效率附录建表语句:
create table test_2(no number,val number)
insert into test_2 values(1,0);
insert into test_2 values(2,0);
insert into test_2 values(3,0);
insert into test_2 values(4,0);
insert into test_2 values(5,0);create table test_1(id number,val number)
insert into test_1 values(1,1);
insert into test_1 values(2,2);
insert into test_1 values(3,3);
insert into test_1 values(4,4);
insert into test_1 values(5,5);
test_2中的 VAL 就会被NULL 覆盖应该
update test_2 set val=(select val from test_1 where id=test_2.no)
where test_1.id=test_2.no;
where test_1.id=test_2.no;执行时报错啊
还有就是有没有更有效率的,因为是在几百万的表中执行;
谢谢
update (select a.id id1,a.val1,b.no no,b.val2 from test_1 a,test_2 b )
set val2=val1 where id1=no;
update (select a.id id1,a.val1,b.no no,b.val2 from test_1 a,test_2 b where a.id=b.no)
set val2=val1 where id1=no;