A 表 TBR 填报人 dept 所部门属
B 表 ACCOUNT 账号 dept 所部门属
现在A表 部门字段出现空值 属于垃圾数据,要填写上部门
A表和B表 通过 TBR = ACCOUNT 关联 目的就是找出A表的dept为空的数据 根据关联从B表找出部门填写到A表上 我不会写 请高手赐教
B 表 ACCOUNT 账号 dept 所部门属
现在A表 部门字段出现空值 属于垃圾数据,要填写上部门
A表和B表 通过 TBR = ACCOUNT 关联 目的就是找出A表的dept为空的数据 根据关联从B表找出部门填写到A表上 我不会写 请高手赐教
update A set A.dept=(select B.dept from B where A.TBR=B.ACCOUNT) where A.dept is null;
没有的话
UPDATE a
SET a.dept = (SELECT b.dept
FROM b
WHERE a.tbr = b.ACCOUNT
AND ROWNUM=1)
WHERE a.dept IS NULL;同一个账号会对应不同的部门的话,就看你怎么取部门了,如果也是随意的话那和上面的那个一样
update tabA a
set a.dept = (select b.dept
from tabB b
where a.TBR = b.ACCOUNT
and a.dept is null)
where exists (select 1
from tabA a, tabB b
where a.TBR = b.ACCOUNT
and a.dept is null);
commit;
select A.TBR , NVL(A.DEPT,B.DEPT) FROM A,B
WHERE A.TBR = B.ACCOUNT(+) ;
set a.dept = (select b.dept
from tabB b
where a.TBR = b.ACCOUNT
and a.dept is null)
where exists (select 1
from tabA a, tabB b
where a.TBR = b.ACCOUNT
and a.dept is null);
commit;
update A set A.dept=(select B.dept from B where A.TBR=B.ACCOUNT) where A.dept is null;
set a.dept = (select b.dept
from tabB b
where a.TBR = b.ACCOUNT
and a.dept is null)
where exists (select 1
from tabA a, tabB b
where a.TBR = b.ACCOUNT
and a.dept is null);
commit;
(select b.dept from b where a.TBR=b.account and rownum=1)
where a.dept is null;
set a.dept = (select b.dept
from tabB b
where a.TBR = b.ACCOUNT
and a.dept is null)
where exists (select 1
from tabA a, tabB b
where a.TBR = b.ACCOUNT
and a.dept is null);
commit;
set a.dept = (select b.dept
from tabB b
where a.TBR = b.ACCOUNT
and a.dept is null)
where exists (select 1
from tabA a, tabB b
where a.TBR = b.ACCOUNT
and a.dept is null);
commit;
update A
set A.dept=(select B.dept
from B
where A.TBR=B.ACCOUNT)
where A.dept is null