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.