[oracle] update和merge语句的几点写法

1.update t2 set parentid=(select ownerid from t1 where t1.id=t2.id); 

2. 
update tb_client_win_lost_report a set a.rolling_code_id=2 
where game_code_id=70000 
and exists 
(select 'x' from (select a.id 
from (select id,level_ from tb_admin_role connect by prior id=parent_id start with id =1) a, 
(select lv_id from tb_rolling_plan where rolling_code_id = 2 and game_code_id=70000) b 
where b.lv_id=a.id) c where a.role_id=c.id) 
and rolling_code_id=1 

3. 
update (select rolling_code_id from tb_client_win_lost_report a,temp_role_id b 
where a.role_id=b.id 
and rolling_code_id=1) a set a.rolling_code_id=2; 

4. 
update tb_client_win_lost_report a set a.rolling_code_id=2 
where game_code_id=70000 
and exists 
(select 'x' from (select id from temp_role_id) c where a.role_id=c.id) 
and rolling_code_id=1 
and rownum<100000; 
commit; 

5.

update 多个字段的写法 
update a set (c1,c2,c3) =(select b1,b2,b3 from b where......) where ......; 

 

merge语法

MERGE INTO table_name alias1  
USING (table|view|sub_query) alias2 
ON (join condition)  
WHEN MATCHED THEN      
　　UPDATE table_name      
　　　　SET col1 = col_val1, col2 = col2_val  
WHEN NOT MATCHED THEN
       INSERT (column_list) VALUES (column_values);

merge into /*+ PARALLEL(t,4) */ QUESTIONNAIRE_20161011 t3
using ACCOUNT_FIRST_LAST_20161011 t
ON (t3.acount_id = t.account_id)
when matched then
  update 
   set t3.first_login_date = t.min_record_date
      , t3.last_login_date =t.max_record_date;
commit;

--根据首登计算次留
merge into QUESTIONNAIRE_20161011 t3
using ( select distinct record_date, account_id from LOGIN_20161011) t
ON (t3.acount_id = t.account_id and t3.first_login_date+1 = t.record_date )
when matched then
  update set t3.day2 = 1;
commit;


--充值
merge into QUESTIONNAIRE_20161011 t3
using (select account_id, sum(pay_total) pay_total from PAY_20161011
    group by account_id
    ) t
ON (t3.acount_id = t.account_id )
when matched then
  update set t3.pay_total = t.pay_total;
commit;