A 中 只有XH 这个字段 B 中有userid 和 ZT 两个字段只要 A中的XH 在 B表中存在的话 就更新 B表的字段ZT=2
update b set zt = 2 where exists(select 1 from a where a.xh = b.userid);
codearts 写成 存储过程 谢谢
update B b set(zt = 2) where b.userid in (select xh from A)
create or replace procedure updateZT is begin update B b set zt='2' where b.userid in (select xh from A); commit; exception when others then rollback; end;
如果表比较大 建议 create or replace procedure text is begin execute immediate 'create table temp as select B.userid userid , B.zt zt , A.XH XH from B,A WHERE B.USERID = A.XH(+)';execute immediate 'truncate table B'; INSERT INTO B SELECT userid , CASE WHEN XH IS NULL THEN ZT ELSE '2' END FROM TEMP; COMMIT;EXECUTE IMMEDIATE 'DROP TABLE TEMP'; END TEXT;
set zt = 2
where exists(select 1 from a where a.xh = b.userid);
codearts 写成 存储过程 谢谢
is
begin
update B b set zt='2' where b.userid in (select xh from A);
commit;
exception
when others then
rollback;
end;
create or replace procedure text
is
begin execute immediate 'create table temp as
select B.userid userid ,
B.zt zt ,
A.XH XH
from B,A
WHERE B.USERID = A.XH(+)';execute immediate 'truncate table B';
INSERT INTO B
SELECT userid ,
CASE WHEN XH IS NULL THEN ZT ELSE '2' END
FROM TEMP;
COMMIT;EXECUTE IMMEDIATE 'DROP TABLE TEMP';
END TEXT;