表结构:
表: pro_eccodeorgid eccode
21 22323232
23 11111111
表: usr_org orgid branchid
21 100
23 200
我想将pro_eccode表根据orgid字段 到usr_org表 关联取出branchid,关将eccode 的前三位值更新成branchid的值.
一条sql如何搞定?
表: pro_eccodeorgid eccode
21 22323232
23 11111111
表: usr_org orgid branchid
21 100
23 200
我想将pro_eccode表根据orgid字段 到usr_org表 关联取出branchid,关将eccode 的前三位值更新成branchid的值.
一条sql如何搞定?
set eccode=(select b.eccode||substr(a.eccode,4)
from pro_eccode a,usr_org b
where a.orgid=b.orgid
and b.orgid=c.orgid);
set a.eccode = (select branchid
from (select orgid, branchid
from (select t1.orgid, t2.branchid
from pro_eccode t1, usr_org t2
where t1.orgid = t2.orgid)
where rownum <= 3)
where orgid = a.orgid)
update pro_eccode set eccode=(select branchid ||substr(pro_eccode.eccode,4) from usr_org where orgid=pro_eccode.orgid)
update
( select p.eccode p_eccode, u.branchid u_branchid
from pro_eccode p, usr_org u
where p.orgid = u.orgid
)
set
p_eccode = u_branchid || substr(p_eccode, 4)
update pro_eccode c
set eccode=(select b.eccode||substr(c.eccode,4)
from usr_org b
where b.orgid=c.orgid);
2 set eccode = (select branchid || substr(eccode, 4)
3 from usr_org
4 where orgid = t.orgid)
5 where exists (select 1
6 from usr_org
7 where orgid = t.orgid);
2 rows updated
SQL> select * from pro_eccode;
ORGID ECCODE
---------- ----------
21 10023232
23 2001111
--试一下:--如branchid和eccode字段是数值型的,则:
update pro_eccode b
set b.eccode = to_number((select to_char(branchid) from usr_org a where a.orgid = b.orgid) || substr(to_char(eccode),4,5))
where exists
(select 1 from pro_eccode a, usr_org b where a.orgid = b.orgid);
commit;--如branchid和eccode字段是字符型的,则:
update pro_eccode b
set b.eccode = (select branchid from usr_org a where a.orgid = b.orgid) || substr(eccode,4,5)
where exists
(select 1 from pro_eccode a, usr_org b where a.orgid = b.orgid);
commit;
(SELECT SUBSTR(ECCODE,1,3) FROM PRO_ECCODE WHERE PRO_ECCODE.ORGID=USR_ORG.ORGID);
COMMIT;
如果没有理解错楼主的意思,应该是这样的
(SELECT B.BRANCHID||SUBSTR(A.ECCODE,4,8)
FROM PRO_ECCODE A,USR_ORG B WHERE A.ORGID=B.ORGID=C.ORGID);
COMMIT;
如果没有理解错楼主的意思,应该是这样的