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+ = t.record_date )
when matched then
update set t3.day2 = ;
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;