recordToUpdate and batchUpdate

Scene 01

The pattern the helpers replace

Lesson 20 showed that update never reads the row. When you need the old values, you write three steps. You read the row with for update. You keep a copy as the old record. You change another copy and pass both to update. These three steps are the safe way to change a stored row. Two table helpers do them for you. Record to update works on one record. Batch update works on the rows a query selects.

Example & screen notes

code from lesson 20, with the three steps numbered in the margin.

row = ticket.query(columns='$num,$subject', where='$num=1', for_update=True).fetchRecord()  # 1
old = dict(row)                                                                            # 2
ticket.update(dict(old, subject='first, edited'), old)                                     # 3

Bullets: 1. read the row with a lock 2. keep a copy as old_record 3. change another copy and pass both to update

Scene 02

recordToUpdate: one record

Record to update is used in a with statement. On entry it reads the record with for update, and it keeps a copy as the old record. The with statement gives you the record. You change it in the block. On exit, the helper calls update with the record and the old record. So every hook, field trigger and relation action of lesson 21 runs, and each of them receives the old record.

Example & screen notes

code.

with invoice.recordToUpdate(invoice_id) as record:
    record['total'] = 120

Diagram below, three boxes left to right: "enter: read with for_update, copy as old_record" → "your block changes record" → "exit: update(record, old_record)".

Scene 03

Delete, and errors in the block

The same helper can delete. Set the key of the record to False inside the block. On exit, the helper then calls delete with the old record, and the delete cycle runs. When the block raises an exception, the helper writes nothing. The exception reaches the caller. The plan also gives record to update a branch that inserts a missing record. That branch creates a new record from the table defaults. This course does not cover it.

Example & screen notes

code, then two bullets.

with invoice.recordToUpdate(invoice_id) as record:
    record['id'] = False

the key set to False → delete(old_record) on exit
an exception in the block → nothing is written

Scene 04

batchUpdate: the selected rows

Batch update runs a query with the parameters you pass, such as where and its values. The rows are read with for update. For each row, it builds a copy and applies the updater. The updater is a dictionary merged into the row, or a callable that changes the row in place. When the callable returns False, the row is skipped. Then batch update calls update with the new row and the old row. So each row runs the full update cycle. The result is the list of the keys that were updated.

Example & screen notes

two calls.

invoice.batchUpdate({'vat_total': 0}, where='$customer_id = :c', c=customer_id)

def apply_vat(row):
    if row['total'] is None:
        return False
    row['vat_total'] = row['total'] * Decimal('0.20')
    row['gross_total'] = row['total'] + row['vat_total']

keys = invoice.batchUpdate(apply_vat, where='$date >= :d', d=date(2026, 1, 1))

Bullets: - updater: a dict merged into each row, or a callable that changes the row - a callable that returns False skips the row - returns the keys of the updated rows

Scene 05

The variant with the sql_* commands

The plan gives both helpers a variant that ends with the sql commands of lesson 24, instead of the full cycle. The read with a lock stays the same. The write then runs no hooks. The plan does not fix the name of the argument that selects the variant, so this lesson does not show it.

Example & screen notes

slide with two rows.

helper	default	variant
`recordToUpdate`	`update`, `delete`	`sql_update`, `sql_delete`
`batchUpdate`	`update`	`sql_update`

Scene 06

Logical deletion is an update

asqueel has no command for logical deletion. A row is logically deleted when its deletion column holds a timestamp. With system fields, that column is underscore underscore del TS. To delete rows logically, update that column, for example with batch update. The update cycle runs for each row, so the hooks run and the modification timestamp changes. Queries then leave those rows out, as lesson 18 explained. To restore a row, set the column back to None. The delete command always removes the row from the table.

Example & screen notes

code.

now = datetime.now(timezone.utc)
invoice.batchUpdate({'__del_ts': now}, where='$customer_id = :c', c=customer_id)

Bullets: - __del_ts is the logicalDeletionField of sys_fields - the update cycle runs: hooks, __mod_ts, __mod_user - queries then exclude the rows; delete() always removes the row

Scene 07

Recap

Record to update reads one record with a lock and writes it back at the end of the with block. Batch update does the same for every row of a query and returns the keys. Logical deletion is an update of the deletion column. The next chapter covers transactions and connections.

Example & screen notes

three bullets. - recordToUpdate: read with a lock, change in a with block, update (or delete) on exit - batchUpdate(updater, where=...): the same for every selected row; returns the keys - logical deletion: an update of the deletion column

Next: "Lesson 27 — Transactions and connections".