Add bulk update API for set-based updates without raw SQL

[seapagan]

Problem

Applications using sqliter-py currently need to drop to raw SQL for efficient set-based updates.

In cogitus, we recently optimized group reassignment from N per-row updates to a single SQL statement:

UPDATE ideas SET group_id = ? WHERE group_id = ?

This improved performance, but it bypasses the ORM abstraction. The original goal for using sqliter-py was to avoid embedding SQL in application repositories.

Why this matters

• Performance: Per-row update loops are slow for large result sets.
• Abstraction leak: Apps must know table/column details and write SQL directly.
• Consistency: sqliter-py already supports bulk insert patterns; bulk update is the missing sibling.

Proposal (high level)

Add a bulk update capability that supports set-based updates while keeping ORM ergonomics.

Possible API directions:

1. Query-style update:
   • db.update_where(Model, where={...}, values={...}) -> int
2. Fluent query update:
   • db.select(Model).filter(...).update(values={...}) -> int

Either should:

• Accept model field names (not raw column names).
• Reuse existing filter semantics where possible.
• Return affected row count.
• Parameterize values safely.

Initial use case

IdeaRepository.bulk_move_group in cogitus should be able to become ORM-only again:

• from inline SQL update
• to a sqliter-py bulk update call returning moved row count

Suggested implementation outline

• Build SQL from model metadata + existing filter compiler.
• Reuse parameter binding path used in select/filter.
• Validate values against model fields.
• Return cursor.rowcount.
• Add tests for:
  • simple equality filter update
  • zero rows updated
  • multiple rows updated
  • invalid field names
  • transactional behavior

Follow-up

Once this lands, downstream apps (including cogitus) can remove inline SQL and use the new API.