• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1579
  • Last Modified:

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
0
hydroIT
Asked:
hydroIT
  • 4
  • 3
1 Solution
 
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
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now