update table2 t2 set old = (select new from table1 t1 where t1.a=t2.a and t1.b=t2.b)
update table1 set new=(select table2.old from table2 where a=table2.a and b=table2.b)
呵呵,不难 update table1 set new = (select table2.old from table2 where table2.a = table1.a and table2.b = table1.b);
错误,table1和table2记录多条,符合“table2.a=table1.a and table2.b=table1.b“ 有多条记录。好好想一想................
table1和table主键都a 和 b
难道不是这个意思? SQL> select * from d;PERSON_ID SUPERVISOR ---------- ---------- aa bb bb cc cc dd ee ffSQL> select * from f;PERSON_ID SUPERVISOR ---------- ---------- aa aa bb ccSQL> update f set supervisor_id=(select supervisor_id from d where d.person_id=f.person_id);已更新4行。SQL> select * from f;PERSON_ID SUPERVISOR ---------- ---------- aa bb aa bb bb cc cc ddSQL>
用过程实现不就简单: declare cursor t_sor is select old from table2 where (a,b) in (select a,b from table1); begin for v_sor in t_sor loop update table1 set new=v_sor.old; end loop; end;
sorry,修改以下: declare cursor t_sor is select * from table2; begin for v_sor in t_sor loop update table1 set new=v_sor.old where (a,b) in (v_sor.a,v_sor.b); end loop; end; /
同意楼上。 用 update table1 set new = (select table2.old from table2 where table2.a = table1.a and table2.b = table1.b); where子句返回的值是多行时,是无法执行的,所以可用循环来控制。
同意管主! 有没有用SQL语句直接解决的。
错误,table1和table2记录多条,符合“table2.a=table1.a and table2.b=table1.b“ 有多条记录。好好想一想................ 晕:你给的数据不会返回多条的。
很抱歉,本人知识肤浅。非常抱歉........................... 感谢大家的指教,各位讲的对,是我错了................ 其中: update table2 new=(select old from table1 where table1.a=table2.a and table1.b=table2.b); 这是正确的!!!!!!!!!!!!!!!!!
(select new from table1 t1
where t1.a=t2.a
and t1.b=t2.b)
update table1
set new = (select table2.old
from table2
where table2.a = table1.a
and table2.b = table1.b);
SQL> select * from d;PERSON_ID SUPERVISOR
---------- ----------
aa bb
bb cc
cc dd
ee ffSQL> select * from f;PERSON_ID SUPERVISOR
---------- ----------
aa
aa
bb
ccSQL> update f set supervisor_id=(select supervisor_id from d where d.person_id=f.person_id);已更新4行。SQL> select * from f;PERSON_ID SUPERVISOR
---------- ----------
aa bb
aa bb
bb cc
cc ddSQL>
table1(a,b,new) table2(a,b,old)
---- ---- ------- ------ --------- ----------
a1 b1 a1 b1 old1
a2 b1 a2 b1 old2
a3 b1 a3 b1 old1
a1 b2 a1 b2 old8
a2 b2 a2 b2 old8
a3 b3 a3 b3 old1000
这是table1和table2
实现的任务:table1(a,b,new) table2(a,b,old)
---- ---- ------- ------ --------- ----------
a1 b1 old1 a1 b1 old1
a2 b1 old2 a2 b1 old2
a3 b1 old1 a3 b1 old1
a1 b2 old8 a1 b2 old8
a2 b2 old8 a2 b2 old8
a3 b3 old1000 a3 b3 old1000
declare
cursor t_sor is
select old from table2
where (a,b) in (select a,b from table1);
begin
for v_sor in t_sor loop
update table1 set new=v_sor.old;
end loop;
end;
declare
cursor t_sor is
select * from table2;
begin
for v_sor in t_sor loop
update table1 set new=v_sor.old where (a,b) in (v_sor.a,v_sor.b);
end loop;
end;
/
用
update table1
set new = (select table2.old
from table2
where table2.a = table1.a
and table2.b = table1.b);
where子句返回的值是多行时,是无法执行的,所以可用循环来控制。
有没有用SQL语句直接解决的。
晕:你给的数据不会返回多条的。
你的“过程“也不对,“update语句“错误!!!!!!!!!!!!!
系统出错:“过程不允许将值列表作为自变量 ”
只要table2的主键是(a,b)的话,
(select table2.old from table2 where a=table2.a and b=table2.b)不可能返回多条!
你的问题到底是什么意思?
update table1
set new = table2.old from table1,table2
where table2.a = table1.a and table2.b = table1.b
-- --- --------
1 2 t2
2 1 t1
1 3 t3
1 5 t4
1 6 t5
1 7 t6
1 8 t7
1 9 t8
1 10 t9
1 11 t10SQL> select * from test2;BH BH2 OLDV
-- --- --------
1 2 t1
2 1 t1
1 3 t1
1 5 t1
1 6 t1
1 7 t1
1 8 t1
1 9 t1
1 10 t1
1 11 t1
SQL> update test2 set oldv = (select test1.newv from test1 where test1.bh1 = test2.bh1 and test1.bh2 = test2.bh2);已更新10行。
SQL> select * from test2;BH BH2 OLDV
-- --- --------
1 2 t2
2 1 t1
1 3 t3
1 5 t4
1 6 t5
1 7 t6
1 8 t7
1 9 t8
1 10 t9
1 11 t10已选择10行。
不是你要的结果吗?????
感谢大家的指教,各位讲的对,是我错了................
其中:
update table2 new=(select old from table1 where table1.a=table2.a and table1.b=table2.b);
这是正确的!!!!!!!!!!!!!!!!!