Mise Updates

Updates

An UPDATE command names a target and one or more SET assignments. Add predicates to select which rows change, then execute it through DbWriter.

Update selected rows

var command = new PostgreSqlCommandBuilder()
    .Update($"{CourseTbl.Table:raw}")
    .Set($"title = {title}")
    .Set($"capacity = {capacity}")
    .Where($"{CourseTbl.CourseIdCol:raw} = {courseId}");

Result<int> updated = await writer.ExecuteAsync(command, cancellationToken);

Repeated SET calls add comma-separated assignments. Use unqualified target column names on the left of assignments; generated column constants include table qualifiers and are useful in expressions and predicates.

Runtime values are bound parameters. [Computed] and [ExcludeFromUpdate] do not filter assignments automatically; leave those columns out explicitly.

Interpret the row count

Zero affected rows is a successful Result<int> containing zero. It does not become NotFound. If the application treats a missing target as failure, inspect the count and return your chosen outcome.

Affected-row semantics come from the provider. Some databases or connection settings distinguish matched rows from rows whose values changed. Avoid treating the count as a universal proof of a value change.

For optimistic concurrency, include the expected version in the predicate:

.Where($"course_id = {courseId}")
.Where($"version = {expectedVersion}")
.Set($"version = version + 1")

This fragment assumes a version column in your schema. Zero affected rows can mean either a missing course or a stale version; your application decides whether to distinguish those cases.

Set from a scalar query

var maximumCapacity = new PostgreSqlQueryBuilder()
    .Select($"MAX({CourseTbl.CapacityCol:raw})")
    .From($"{CourseTbl.Table:raw}");

var command = new PostgreSqlCommandBuilder()
    .Update($"{CourseTbl.Table:raw}")
    .Set("capacity", maximumCapacity)
    .Where($"{CourseTbl.CourseIdCol:raw} = {courseId}");

Set(column, query) quotes the target column name and embeds the child as a scalar subquery. The child must be a Mise builder using the same engine and produce a value compatible with the target column. It is not a separate preliminary read.

Scope and results

An UPDATE without WHERE changes every row in the target. Mise permits that statement; include the intended predicates explicitly. The target can be set only once, and UPDATE requires at least one SET assignment.

Use returning commands when you need the changed row rather than a count. PostgreSQL and SQLite support UPDATE RETURNING; SQL Server supports OUTPUT INSERTED. See Returned rows, Transactions, and Deletes.