Batch Update in SQL

Update statement can be done as a bulk update when updating multiple records but there is sometime a need to update records in batches so that the log file does not get filled up or the table is not locked for long time.

 

SQL Server:

Script:

WHILE (1 > 0)

BEGIN

BEGIN TRANSACTION

UPDATE TOP ( 10000 ) table1

SET col1= table2.col4,

col2 =  table2.col5

from table1

inner join table2

on table1.id=table2.id

WHERE col1<> table2.col4

or col2 <>  table2.col5

IF @@ROWCOUNT = 0

BEGIN

COMMIT TRANSACTION

BREAK

END

COMMIT TRANSACTION

-- 10 second delay if needed

WAITFOR DELAY '00:00:10'

END -- end WHILE

 

Oracle:

Script:

DECLARE

v_fetchlimit number := 10000

CURSOR table_cur IS

SELECT id, col4, col5

FROM   table2;  --cursor is used if we are not using all source column

type table_type  IS TABLE OF table_cur%ROWTYPE  INDEX BY PLS_INTEGER; --this will create a type for table variable

table2_tab table_type; --this statement creates the actual table variable

BEGIN

open table_cur;

LOOP

fetch table_cur bulk collect into table2_tab limit v_fetchlimit; --this statement loads records into table variable

exit when table2_tab.count = 0;

FORALL i in table2_tab.FIRST .. table2_tab.LAST LOOP

update table1

set col1=table2_tab.col4(i)

,col2=table2_tab.col5(i)

where table1.id=table2_tab.id(i);

commit;

END LOOP;

close table_cur;

END;

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.