Oracle update statement not updating

The fourth inserts the row, then performs an update if the insert fails with a duplicate value on index exception.

l_LOOP BEGIN INSERT INTO dest_tab (object_id, owner, object_name, object_type) VALUES (l_tab(i).object_id, l_tab(i).owner, l_tab(i).object_name, l_tab(i).object_type); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE dest_tab SET owner = l_tab(i).owner, object_name = l_tab(i).object_name, object_type = l_tab(i).object_type WHERE object_id = l_tab(i).object_id; END; END LOOP; DBMS_OUTPUT.put_line('INSERT/UPDATE: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs'); ROLLBACK; END; / MERGE : 119 hsecs ROW MERGE : 1453 hsecs UPDATE/INSERT: 1280 hsecs INSERT/UPDATE: 2443 hsecs PL/SQL procedure successfully completed.

In Oracle, when you need to update values of certain records from one table, and the value and the records to update are determined by a second table, you can use a subquery in the update statement to achieve the updating efficiently.

Experts, I wanted to join two tables and update a single column as -1 This statement is giving errors.

update tab a set col1 = -1 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 Can anyone throw some light on this? I looks like it should be something like this: update a set col1 = -1 from tab a, tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4 HTH, Ananthram" id="ctl00_m_m_i_ctl00_gr_ctl01_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5190317" / update tab a set col1 = -1 where exists (select 1 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Method 2: update tab a set (col1,col7,col8) = (select -1, col7,col8 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) where exists (select 1 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Note: In Method 2 the sub-query must return only 1 row.

set (col1,col7,col8) = (select -1, col7,col8 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Don" id="ctl00_m_m_i_ctl00_gr_ctl05_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5190407" / Method 1: update tab a set col1 = -1 where exists (select 1 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Method 2: update tab a set (col1,col7,col8) = (select -1, col7,col8 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) where exists (select 1 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Note: In Method 2 the sub-query must return only 1 row.

set (col1,col7,col8) = (select -1, col7,col8 from tab b where a.col2 = b.col2 and a.col3 = b.col3 and a.col3 = b.col4 ) Don clause (for the UPDATE portion), but no WHEN NOT MATCHED clause (since no INSERT is intended).I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I'm going to use comes from the accounting department. In sum, accounting wants to update job 103's invoice amount to 0 and job 104's invoice amount to 0, while leaving all other amounts the same.In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records. update tabb b set new_id = (select new_id from taba a where a.old_id = b.old_id) where exists (select 1 from taba a where a.old_id = b.old_id) Don" id="ctl00_m_m_i_ctl00_gr_ctl01_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="4885427" / This is one way but there are other ways of this update statement update tabb b set new_id = (select new_id from taba a where a.old_id = b.old_id) where exists (select 1 from taba a where a.old_id = b.old_id) Don This outer where clause " where b.old_id = a.old_id " creates error since it doesn't identify the alais in the inner query .. I am trying to match old client id in 2 tables and get the new id and update it in the tableb for the corresponding old_id the query format I used is update tabb b set new_id = ( select new_id from taba a, tabb b where a.old_id = b.old_id) where b.old_id = a.old_id --- this is the place where I get a problem Thanks.Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed.

Tags: , ,