Use of Update-From for migration
In previous version of SQLite, the UPDATE-FROM was not supported.
This is now allowed since version 3.33.0:
The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows. UPDATE-FROM is supported beginning in SQLite version 3.33.0 (2020-08-14).
https://sqlite.org/lang_update.html#update_from
We could update some migration scripts in a near future to use this feature.
Example Before:
...
dimension_columns = ['length', 'length_uom', 'height', 'height_uom']
columns = []
values = []
for c in dimension_columns:
columns.append(Column(sql_table, c))
values.append(package_type.select(Column(package_type, c),
where=package_type.id == sql_table.type))
cursor.execute(*sql_table.update(
columns=columns,
values=values))
After:
...
dimension_columns = ['length', 'length_uom', 'height', 'height_uom']
cursor.execute(*sql_table.update(
columns=[Column(sql_table, c) for c in dimension_columns],
values=[Column(package_type, c) for c in dimension_columns],
from_=[package_type],
where=package_type.id == sql_table.type))