The update_recordset operator can be used to update a chunk of records in
a table in one database operation. As with the insert_recordset operator
the update_recordset is very efficient because it only needs to call an update in
the database once.
The syntax for the update_recordset operator can be seen in the next example:
static void Update_RecordsetExmple(Args _args)
{
CarTable carTable;
;
info(“BEFORE UPDATE”);
while select carTable
where carTable.ModelYear == 2007
{
info(strfmt(“CarId %1 has run %2 miles”,
carTable.CarId, carTable.Mileage));
}
update_recordset carTable
setting Mileage = carTable.Mileage + 1000
where carTable.ModelYear == 2007;
info(“AFTER UPDATE”);
while select carTable
where carTable.ModelYear == 2007
{
info(strfmt(“CarId %1 has now run %2 miles”,
carTable.CarId, carTable.Mileage));
}
}
When this Job is executed it will print the following messages to the Infolog:
Notice that no error was thrown even though the Job didn’t use selectforupdate,
ttsbegin, and ttscommit statements in this example. The selectforupdate is
implicit when using the update_recordset, and the ttsbegin and ttscommit are
not necessary when all the updates are done in one database operation. However, if
you were to write several update_recordset statements in a row, or do other checks
that should make the update fail, you could use ttsbegin and ttscommit and force
a ttsabort if the checks fail.
Consider modifying the previous example so that it gets a transaction scope and a
check that makes sure to break the update as shown below:
static void Update_RecordsetExmpleTts(Args _args)
{
CarTable carTable;
// Change the check to false to make the
// transaction go through
boolean check = true;
;
info(“BEFORE UPDATE”);
while select carTable
where carTable.ModelYear == 2007
{
info(strfmt(“CarId %1 has run %2 miles”,
carTable.CarId, carTable.Mileage));
}
ttsbegin; // Added ttsbegin
update_recordset carTable
setting Mileage = carTable.Mileage + 1000
where carTable.ModelYear == 2007;
if (check)
throw info(“This is a test”);
ttscommit; // Added ttscommit
info(“AFTER UPDATE”);
while select carTable
where carTable.ModelYear == 2007
{
info(strfmt(“CarId %1 has now run %2 miles”,
carTable.CarId, carTable.Mileage));
}
}
The update will now never be committed in the database so every time you run this
Job the the result will look like the next screenshot:
Source
MicrDynamicAX2009 Started