Oracle Batch Update

Personally, I think it's a good writing.

http://blog.csdn.net/wanglilin/article/details/7200201

Demand:

Update the records in the T2 (T_STATBUF) table with the same ID as the T1 (T_MT) table into the T1 table.

1. The wrong wording:

1 Update table_name T1 set (A,B,C) = (select A,b,c from table_name_2 T2 where t1.a=t2.a);

In this notation, all rows in the T1 table are updated: If t1.a=t2.a, the records detected in T2 are updated to T1, and if t1.a<>t2.a, the records in T1 are updated to empty (null).

The correct wording:

1 Update table_name T1 set (A,B,C) = (select A,b,c from table_name_2 T2 where t1.a=t2.a) 2 where exists (select 1 from Table_ Name_2 T2 where t1.a=t2.a);  

Analytical:

The correct way to do this is to add a exists in the back (select 1 from table_name_2 T2 where t1.a=t2.a);

This sentence means: if there is t1.a=t2.a, update, otherwise, do not update, so will not cause all the records in the T1 table are updated.

Cases:

Update Table_name_1 Set (A, b) = (select from dual where 1=2);

This result will update all the records in the table_name_1 to null (NULL), since the 1=2 behind is not valid.

Summarize:

Update, to understand the qualification, to test!

My test statement:

1 update my_time_test1 T1 set (mdate,discript) = (select Mdate,discript from   2 my_time_test T2 where T1. Discript=t2. Discript) where exists (select 1 from   3 my_time_test T2 where T1. Discript=t2. Discript);  

My business statement:

1 update t_mt T1 set (stat,ostat,rpttime) = (  2 Select Stat,stat,rpttime from T_statbuf T2 where T1. Msgid=t2. MSGID) where exists (  3 select 1 from t_statbuf T2 where T1. Msgid=t2. MSGID);  --if there are T1 and T2 equal, update. Does not add where exists, whether the deposit does not exist, is updated, does not exist, the results will be updated to empty, but the record is still  

Oracle Bulk Update