update b set num=(select num from a where id=b.aid)
应用游标技术解决 CURSOR C_2 IS SELECT DISTINCT id,num FROM a; VID TABLE1.ID%TYPE; VNUM TABLE1.date%TYPE; OPEN C_2; LOOP FETCH C_2 INTO VID,VNUM ; EXIT WHEN C_2%NOTFOUND; BEGIN UPDATE b SET num=VNUM WHERE ID=VID; END; END LOOP; CLOSE C_2;
应用游标技术解决 CURSOR C_2 IS SELECT DISTINCT id,num FROM a; VID a.ID%TYPE; VNUM a.date%TYPE; OPEN C_2; LOOP FETCH C_2 INTO VID,VNUM ; EXIT WHEN C_2%NOTFOUND; BEGIN UPDATE b SET num=VNUM WHERE ID=VID; END; END LOOP; CLOSE C_2;
顶。2楼的SQL未把ID有重复的时候考虑进去
create table ta(id int,name varchar2(10),num int); insert into ta values(1,'a',1); insert into ta values(2,'a',2); insert into ta values(3,'a',3);create table tb(id int,num int); insert into tb(id) values(1); insert into tb(id) values(2);update tb b set b.num=(select num from ta a where a.id=b.id) where b.id in (select a.id from ta a where a.id=b.id);select * from tb
尽量不要用游标,会把程序速度带慢的如果怕id有重复可以这样 update b set num=(select max(num) from a where id=b.aid)
CURSOR C_2 IS
SELECT DISTINCT id,num FROM a;
VID TABLE1.ID%TYPE;
VNUM TABLE1.date%TYPE;
OPEN C_2;
LOOP
FETCH C_2 INTO VID,VNUM ;
EXIT WHEN C_2%NOTFOUND;
BEGIN
UPDATE b SET num=VNUM WHERE ID=VID;
END;
END LOOP;
CLOSE C_2;
CURSOR C_2 IS
SELECT DISTINCT id,num FROM a;
VID a.ID%TYPE;
VNUM a.date%TYPE;
OPEN C_2;
LOOP
FETCH C_2 INTO VID,VNUM ;
EXIT WHEN C_2%NOTFOUND;
BEGIN
UPDATE b SET num=VNUM WHERE ID=VID;
END;
END LOOP;
CLOSE C_2;
create table ta(id int,name varchar2(10),num int);
insert into ta values(1,'a',1);
insert into ta values(2,'a',2);
insert into ta values(3,'a',3);create table tb(id int,num int);
insert into tb(id) values(1);
insert into tb(id) values(2);update tb b set b.num=(select num from ta a where a.id=b.id)
where b.id in (select a.id from ta a where a.id=b.id);select * from tb
update b set num=(select max(num) from a where id=b.aid)