Section 2.14. Multiple Updates at Once

2.14. Multiple Updates at Once

My basic assertion here is that successive updates to a single table are acceptable if they affect disjoint sets of rows; otherwise they should be combined. For example, here is some code from an actual application:^[]

] Table names have been changed.

     update tbo_invoice_extractor     set pga_status = 0     where pga_status in (1,3)       and inv_type = 0;     update tbo_invoice_extractor        set rd_status = 0      where rd_status in (1,3)        and inv_type = 0;

Two successive updates are being applied to the same table. Will the same rows be hit twice? There is no way to tell. The question is, how efficient are the search criteria? Any attribute with a name like type or status is typically a column with a totally skewed distribution. It is quite possible that both updates may result in two successive full scans of the same table. One update may use an index efficiently, and the second update may result in an unavoidable full table scan. Or, fortuitously, both may be able to make efficient use of an index. In any case, there is almost nothing to lose and everything to win by trying to combine both updates into a single statement:

     update tbo_invoice_extractor     set pga_status = (case pga_status                         when 1 then 0                         when 3 then 0                         else pga_status                       end),          rd_status = (case rd_status                         when 1 then 0                         when 3 then 0                         else rd_status                        end)     where (pga_status in (1,3)            or rd_status in (1, 3))       and inv_type = 0;

There is indeed the possibility of some slight overhead due to the update of some columns with exactly the same contents they already have. But in most cases, one update is a lot faster than several separate ones. Notice that in regard to the previous section on logic, how we have used implicit conditional logic, by virtue of the case statement, to process only those rows that meet the update criteria, irrespective of how many different update criteria there may be.

Apply updates in one fell swoop if possible; try to minimize repeated visits to the same table.