merge view subquery的关联更新及 BYPASS_UJVC

create table t1 (
FNAME varchar2(10),
FMONEY varchar2(10)
);

insert into t1 (FNAME, FMONEY)
values ('A', '20');

insert into t1 (FNAME, FMONEY)
values ('B', '30');

select * from t1   ;

create table t2 (
FNAME varchar2(10),
FMONEY varchar2(10)
);

insert into t2 (FNAME, FMONEY)
values ('C', '10');

insert into t2 (FNAME, FMONEY)
values ('D', '20');

insert into t2 (FNAME, FMONEY)
values ('A', '100');

select * from t2  ;

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME);

select *from t1;
select *from t2;

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);

UPDATE  /*+ BYPASS_UJVC use_hash(t1,t2) leading(t1) */ (  
select /*+ BYPASS_UJVC use_hash(t1,t2) leading(t1) */  t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2;

--update T1，PK建在T1上是没有用的-- ORA-01779: cannot modify a column which maps to a non key-preserved table

 
--ORA-01779: 无法修改与非键值保存表对应的列

alter table T1 add constraint T1_KEY1 primary key (FNAME);
alter table T2 add constraint T2_KEY1 primary key (FNAME);
alter table T2 drop constraint T2_KEY1 ;
alter table T1 drop constraint T1_KEY1 ;

--------------------------DELETE VIEW（subquery）的情况-----------------------------------------
 

--没有测试出来两个表数据全部被删除的情况

--那个上面有pk 删除的是另一个表上的，如果两个都有，和表的顺序有关

DELETE /*+ BYPASS_UJVC*/
FROM (select /*+ BYPASS_UJVC use_hash(t1,t2) leading(t1) */  t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t2 ,t1 where  t2.fname=t1.fname ) ;

--把T1和T2调换位置，删除的是T1表
DELETE /*+ BYPASS_UJVC*/
FROM (select /*+ BYPASS_UJVC use_hash(t1,t2) leading(t1) */  t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t1 ,t2 where  t1.fname=t2.fname ) ;

select *from t1;
select *from t2;

merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then 
  update  set t1.fmoney = t.fmoney;
 
 

var B2 number;
exec :B2:=530;
var B1 number;
exec :B1:=201411;
UPDATE  PRERPT.STATQ_WRTOFF_PLYD_PRE P SET GFLAG = 1 WHERE EXISTS (SELECT 1 FROM PRERPT.STATB_GROUP_ACCOUNT T WHERE T.REGION = :B2 AND T.CYCLE = :B1
AND T.ACCTID = P.ACCTID )

UPDATE (SELECT /*+ BYPASS_UJVC use_hash(p,t) leading(t) */
         p.gflag
          FROM prerpt.statq_wrtoff_plyd_pre p
         INNER JOIN (SELECT DISTINCT acctid
                      FROM prerpt.statb_group_account t
                     WHERE t.region = :b2
                       AND t.cycle = :b1) t
            ON t.acctid = p.acctid)
   SET gflag = 1