# UPDATE

How to update rows in a database table using the generated Update() builder in Socigy.OpenSource.DB v0.2.0.

## Overview

Every generated entity instance exposes an `.Update()` method that returns a fluent builder. By default, the builder updates all non-PK columns and targets the row whose PK matches the instance. You can narrow or change the set of columns updated and add extra `WHERE` conditions on top of the PK filter.

## Default Behavior

Without any field-selection modifier, `Update()` generates a `SET` clause for every non-PK column. The `WHERE` clause is always built from the PK column(s) using the values currently on the instance.

```csharp
// C# — update all non-PK columns for the matching row
user.Name = "Alice Renamed";
user.Score = 42;

int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "score" = $2, ... WHERE "id" = $n
```

`ExecuteAsync()` returns `Task<int>` — the number of rows affected. A return value of `0` means no row matched the PK (the row does not exist or was already deleted).

## Field Selection Strategies

### WithAllFields — Explicit All Columns

Explicitly includes every column, including any that the default behavior might exclude.

```csharp
// C# — update all columns explicitly
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

### WithAllFields().ExceptFields() — All Columns Except Listed

Updates all columns but skips the ones you specify. Useful for preserving immutable audit columns.

```csharp
// C# — update everything except CreatedAt
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExceptFields(x => new object[] { x.CreatedAt })
    .ExecuteAsync();
```

### WithFields — Only Listed Columns

Updates only the columns you specify. Every other column is left unchanged in the database.

```csharp
// C# — update only Name and Email
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.Name, x.Email })
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "email" = $2 WHERE "id" = $3
```

### ExceptFields — All Except Listed

Updates all non-PK columns except the ones you specify. The inverse of `WithFields`.

```csharp
// C# — update all columns except UpdatedAt (managed elsewhere)
int rows = await user.Update()
    .WithConnection(conn)
    .ExceptFields(x => new object[] { x.UpdatedAt })
    .ExecuteAsync();
```

## Additional WHERE Conditions

`.Where()` adds extra conditions to the `WHERE` clause beyond the PK filter. Both the PK condition and the additional conditions must be satisfied for a row to be updated.

```csharp
// C# — only update if the row is currently active
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.Score })
    .Where(x => x.Status == "active")
    .ExecuteAsync();
// SQL: UPDATE "users" SET "score" = $1 WHERE "id" = $2 AND "status" = $3
```

## Static Shorthand

`User.UpdateAsync(instance, conn)` is a static convenience method that updates all non-PK columns for the matching row. It is equivalent to calling `.ExecuteAsync()` with no field-selection modifier.

```csharp
// C# — static shorthand
int rows = await User.UpdateAsync(user, conn);
```

> **NOTE** The static shorthand does not support field selection or extra WHERE conditions. Use the instance builder when you need those options.

## Composite Primary Keys

When an entity has a composite PK (multiple properties marked `[PrimaryKey]`), all PK columns are automatically included in the `WHERE` clause. You do not need to do anything special.

```csharp
// C# — UserCourse has [PrimaryKey] on both UserId and CourseId
enrollment.CompletedAt = DateTimeOffset.UtcNow;

int rows = await enrollment.Update()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.CompletedAt })
    .ExecuteAsync();
// SQL: UPDATE "user_course" SET "completed_at" = $1
//      WHERE "user_id" = $2 AND "course_id" = $3
```

## Update With a Transaction

Pass a `DbTransaction` instead of a `DbConnection` when the update is part of a larger unit of work.

```csharp
// C# — update inside a transaction
await using var tx = await conn.BeginTransactionAsync();

user.Status = "suspended";

int rows = await user.Update()
    .WithTransaction(tx)
    .WithFields(x => new object[] { x.Status })
    .ExecuteAsync();

await tx.CommitAsync();
```

## Interpreting the Return Value

| Return value | Meaning |
|---|---|
| `> 0` | That many rows were updated (usually `1` for PK-based updates) |
| `0` | No matching row found; the instance's PK does not exist in the table |

```csharp
// C# — detect missing row
int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();

if (rows == 0)
    throw new InvalidOperationException($"User {user.Id} not found.");
```

## See Also

- [INSERT](insert) — adding new rows
- [DELETE](delete) — removing rows
- [WHERE Operators](where) — predicate syntax for the .Where() modifier