Bulk Update using Primary Key

Heya folks,

Have you guys any sample code to do an Oracle 9.2i bulk update?

I need to update a large table with a smaller table that is a sub-set of the larger one and has fewer but analogous columns.  A join on ROWIDs is therefore not possible, it has to be done by linking the Primary Keys on the source and destination tables?

Cheers,
HydroIT
hydroITAsked:
Who is Participating?
 
MikeOM_DBACommented:
Maybe you could just try this:
 

DECLARE
   -- source table cursor (only columns to be updated)
   -- new data
   CURSOR new_data_cur
   IS
      SELECT g.ROWID row_id, o.hge
        FROM base_table g, new_data_table o
       WHERE g.gen_asset_type_id = o.gen_asset_type_id  -- Primary key
      ;
 
   TYPE new_data_type IS TABLE OF new_data_cur%ROWTYPE
      INDEX BY PLS_INTEGER;
 
   new_data_tab   new_data_type;
BEGIN
   OPEN new_data_cur;
 
   LOOP
      FETCH new_data_cur
      BULK COLLECT INTO new_data_tab LIMIT 5000;
 
      --1. Add/populate rowid of the base table to new table containing new data.
      --2. If possible, try to sort the new data in the same way like base table. In my case, we sorted the new data by the primary key.
      --    This will reduce multiple reads/writes of the same base table blocks.
      FORALL i IN new_data_tab.FIRST .. new_data_tab.LAST SAVE EXCEPTIONS
         UPDATE base_table
            SET hge = new_data_tab (i).hge
          WHERE ROWID = new_data_tab (i).row_id;
      COMMIT;
 
      EXIT WHEN base_tab.COUNT < 5000;
   END LOOP;
 
   COMMIT;
 
   CLOSE new_data_cur;
END;

Open in new window

0
 
MikeOM_DBACommented:
Try this
 

UPDATE large_table l
   SET (colx, coly, colz) = (SELECT colx, coly, colz
                               FROM small_table s
                              WHERE s.primary_key = l.primary_key)
 WHERE EXISTS (SELECT 'Yes'
                 FROM small_table s
                WHERE s.primary_key = l.primary_key);

Open in new window

0
 
hydroITAuthor Commented:
Many thanks MikeOM,

However, I was looking for the use of a BULK COLLECT to transform my millions of rows update from snail's pace to zipping along,

Cheers,
HydroIT
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
hydroITAuthor Commented:
Here's as close as I've got to a solution:
DECLARE
	-- source table cursor (only columns to be updated)
	CURSOR base_table_cur
	IS
		SELECT    ROWID, hge
          FROM    base_table;
          
	TYPE base_type IS TABLE OF base_table_cur%ROWTYPE INDEX BY PLS_INTEGER;
	base_tab 			base_type;
 
	-- new data
	CURSOR new_data_cur
	IS
		    SELECT    g.ROWID row_id, o.hge
                   FROM    base_table g, new_data_table
                 WHERE  g.gen_asset_type_id = o.gen_asset_type_id -- Primary key  --
            ORDER BY g.gen_asset_type_id;
 
	TYPE new_data_type IS TABLE OF new_data_cur%ROWTYPE INDEX BY PLS_INTEGER;
	new_data_tab		new_data_type;
 
	TYPE row_id_type IS TABLE OF ROWID INDEX BY PLS_INTEGER;
	row_id_tab			row_id_type;
 
	TYPE rt_update_cols IS RECORD (
									 new_hge	                            base_table.hge%TYPE
								  );
 
	TYPE update_cols_type IS TABLE OF rt_update_cols INDEX BY PLS_INTEGER;
	update_cols_tab	update_cols_type;
    
BEGIN
	OPEN base_table_cur;
	OPEN new_data_cur;
 
	LOOP
        FETCH base_table_cur BULK COLLECT INTO   base_tab LIMIT 5000;
        FETCH new_data_cur BULK COLLECT INTO    new_data_tab LIMIT 5000;
 
        --1. Add/populate rowid of the base table to new table containing new data.
        FOR i IN base_tab.FIRST .. base_tab.LAST
        LOOP
            row_id_tab (i) := new_data_tab (i).row_id;
            update_cols_tab (i).new_hge := new_data_tab (i).hge;
        END LOOP;
 
        --2. If possible, try to sort the new data in the same way like base table. In my case, we sorted the new data by the primary key. 
        --    This will reduce multiple reads/writes of the same base table blocks.
        FORALL i IN base_tab.FIRST .. base_tab.LAST
        SAVE EXCEPTIONS
            UPDATE    (SELECT      hge FROM    base_table)
                SET    row = update_cols_tab (i)
             WHERE    ROWID = row_id_tab (i);
 
        COMMIT;
 
        EXIT WHEN base_tab.COUNT < 5000;
    END LOOP;
 
    COMMIT;
 
    CLOSE base_table_cur;
 
    CLOSE new_data_cur;
END;

Open in new window

0
 
MikeOM_DBACommented:
Also this would "zip" along:
 

UPDATE base_table g
   SET hge = (SELECT hge
                FROM new_data_table o
               WHERE g.gen_asset_type_id = o.gen_asset_type_id)
 WHERE EXISTS (SELECT 'Maybe'
                 FROM new_data_table o
                WHERE g.gen_asset_type_id = o.gen_asset_type_id)
;

Open in new window

0
 
hydroITAuthor Commented:
Cheers MikeOM,

This hits the nail on the head and is faster than your suggestion below (though more long-winded) as it uses the BULK BIND,

HyrdoIT
0
 
suhinrasheedCommented:
now this is bulk update for a single column,wat if i had 3 columns to update,what will be the format of below statement in yur code.i knw i can fetch mltiple clmns in my cursor but wat mdfcn shd i do to below

UPDATE base_table
            SET hge = new_data_tab (i).hge
          WHERE ROWID = new_data_tab (i).row_id;
0
 
MikeOM_DBACommented:
Try:


UPDATE base_table
   SET hge  = new_data_tab (i).hge,
       hge2 = new_data_tab (i).hge2,
       hge3 = new_data_tab (i).hge3
 WHERE ROWID = new_data_tab (i).row_id;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.