Batch Updates

When a large number of records - possibly thousands - need to be updated, the DataSource API is not most efficient way of processing the data. It requires that all data is sent from the database to Web Central, loaded into memory, updated using Java code, and sent back to the database. It is faster to process the data directly in the database using SQL.

You can use the SqlUtils object to execute any custom SQL query. Web Central also provide the FieldOperation and FieldFormula objects that can generate and execute SQL queries for most frequently used batch update use cases.

Batch Updates Using Field Operation Object

The FieldOperation object updates a field in the owner table, by applying an operation on a field in the assigned table. For example, you can use FieldOperation to calculate estimated labor cost for all work requests, as a sum of estimated cost of all trades for each request. Each work request record is related to zero or more work request trade records. The operation updates the wr.cost_est_labor field of each work request record, based on wrtr.cost_estimated values in all related work request trade records:

new FieldOperation()
.setOwner("wr")
.setAssigned("wrtr")
.calculate("wr.cost_est_labor", "SUM", "wrtr.cost_estimated");

You can restrict the owner records that will be updated:

new FieldOperation()
.setOwner("wr")
.setAssigned("wrtr")
.addOwnerRestriction("NOT EXISTS (SELECT wr_id FROM wrcf WHERE wr.wr_id = wrcf.wr_id)")
.calculate("wr.cost_est_labor", "SUM", "wrtr.cost_estimated");

You can also restrict assigned records that will be used to calculate values for each owner record:

new FieldOperation()
.setOwner("wr")
.setAssigned("wrtr")
.addAssignedRestriction("wrtr.cost_estimated > 10")
.calculate("wr.cost_est_labor", "SUM", "wrtr.cost_estimated");

The assigned restriction can include values from a standard table. The assigned and the standard tables are joined:

new FieldOperation()
.setOwner("fl")
.setAssigned("rm")
.setStandard("rmcat")
.setAssignedRestriction( "rmcat.occupiable = 1" )
.calculate("fl.area_ocup", "SUM", "rm.area");

Batch Updates Using Field Formula Object

The FieldFormula object updates a field in the assigned table, by applying an SQL formula (expression) on one or more fields in the assigned table, owner table, and/or standard table. For example, you can use FieldFormula to calculated the Area Remaining values for all floor records, based on the Area Gross Internal and the Area Rooms values in each floor:

new FieldFormula()
.setAssigned("fl")
.calculate("fl.area_remain", "fl.area_gross_int - fl.area_rm");

You can join an owner table to an assigned table. The assigned table field will be updated, based on values in both tables. For example:

new FieldFormula()
.setOwner("rm")
.setAssigned("rmpct")
.calculate("rmpct.cost", "rmpct.area_chargable * rm.cost_sqft");

You can restrict assigned records that will be updated:

new FieldFormula()
.setOwner("rm")
.setAssigned("rmpct")
.setAssignedRestriction("rmpct.dp_id IS NOT NULL")
.calculate("rmpct.cost", "rmpct.area_chargable * rm.cost_sqft");

The calculation can also use values from a standard table:

new FieldFormula()
.setOwner("rm")
.setAssigned("rmpct")
.setStandard("rmcat")
.setAssignedRestriction("rmpct.dp_id IS NOT NULL AND rmpct.rm_cat IS NOT NULL ")
.calculate("rmpct.cost", "rmpct.area_chargable * rmcat.cost_sqft");

Batch Updates Using SQL

If the desired operation cannot be implemented using FieldOperation or FieldFormula objects, you can code it as a custom SQL update. For example:

String sql = "UPDATE site SET area_st_comn_gp = (SELECT ${sql.isNull('SUM(gp.area)', 0)} FROM bl, gp WHERE ...)";
SqlUtils.executeUpdate("site", sql);