Oracle加字段然后批量更新语句
本次的业务需求是:在一张旧表t_bnet_customer上增加了一个新的字段,
这个字段在值在另一张表t_customer_extend里面,一一对应来更新;
原先的语句:
www.2cto.com
UPDATE t_bnet_customer a
SET a.customer_id = (SELECT b.cust_nbr FROM t_customer_extend b
WHERE a.id = b.bnet_id )
WHERE EXISTS (SELECT b.cust_nbr FROM t_customer_extend b
WHERE a.id = b.bnet_id)
后来的语句:
update (
select a.customer_id a1, b.cust_nbr b1
from t_bnet_customer a,
t_customer_extend b
where a.id = b.bnet_id
) set a1 = b1 ;
www.2cto.com
上面的语句被oracle限制了,不能执行的话,换成下面的:
update (
select /*+ BYPASS_UJVC */ a.customer_id a1, b.cust_nbr b1
from t_bnet_customer a,
t_customer_extend b
where a.id = b.bnet_id
) set a1 = b1 ;