---
title: "Update SQLite rows from another table in C#"
url: https://recursiveai.org/onelite/cookbook/update-sqlite-rows-from-another-table/
description: "Use SQLite UPDATE FROM in C# with OneLite to copy values by a matching key, leave unmatched rows alone, and avoid ambiguous source matches."
version: "1.0.0"
---

# Update SQLite rows from another table in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/update-sqlite-rows-from-another-table/update-sqlite-rows-from-another-table.zip)

Use `UPDATE … FROM` to copy values into existing rows by a shared key, such as a stock item ID.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
using RecursiveAI.OneLite;

await using var db =
    await Database.OpenAsync(":memory:");
await db.ExecuteScriptAsync("""
    CREATE TABLE stock(id, qty);
    CREATE TABLE incoming(
      id INTEGER PRIMARY KEY, qty);
    INSERT INTO stock VALUES(1, 3), (2, 5);
    INSERT INTO incoming VALUES(1, 8);
    UPDATE stock SET qty = incoming.qty
    FROM incoming
    WHERE stock.id = incoming.id;
    """);
var rows = await db.QueryAsync<string>($"""
    SELECT id || ': ' || qty FROM stock
    ORDER BY id
    """);
foreach (var row in rows)
    Console.WriteLine(row);
```

OneLite binds interpolated values like `{value}` as SQL parameters rather than joining them into the SQL text; `ExecuteAsync` and `QueryAsync` require the `$` prefix even when there are no values to bind.

Prints each stock ID and its resulting quantity.

```text
1: 8
2: 5
```

Unzip [the example](https://recursiveai.org/onelite/cookbook/update-sqlite-rows-from-another-table/update-sqlite-rows-from-another-table.zip), then `dotnet run`.

## The full program

```csharp
using RecursiveAI.OneLite;

await using var db = await Database.OpenAsync(":memory:");
await db.ExecuteScriptAsync("""
    CREATE TABLE stock(id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
    CREATE TABLE incoming(id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
    INSERT INTO stock VALUES(1, 3), (2, 5), (3, 1);
    INSERT INTO incoming VALUES(1, 8), (3, 6), (4, 9);
    """);

var updated = await db.ExecuteAsync($"""
    UPDATE stock AS target
    SET qty = source.qty
    FROM incoming AS source
    WHERE target.id = source.id
    """);
Console.WriteLine($"Rows updated: {updated}");

var rows = await db.QueryAsync<string>($"""
    SELECT id || ': ' || qty FROM stock ORDER BY id
    """);
foreach (var row in rows)
    Console.WriteLine(row);
```

```text
Rows updated: 2
1: 8
2: 5
3: 6
```

The full program matches stock IDs `1` and `3`. ID `2` has no incoming quantity, so its value stays `5`. ID `4` exists only in `incoming`, so nothing is inserted into `stock`. `ExecuteAsync` reports the number of rows updated.

If the join matches no rows, `ExecuteAsync` returns `0`; that isn’t an exception.

## Variations

**Add the incoming quantity instead of replacing it.** In the full program, replace the SQL passed to `ExecuteAsync` with:

```sql
UPDATE stock AS target
SET qty = target.qty + source.qty
FROM incoming AS source
WHERE target.id = source.id;
```

```text
Rows updated: 2
1: 11
2: 5
3: 7
```

## Related recipes

- [Join attached SQLite databases in C#](https://recursiveai.org/onelite/cookbook/join-attached-sqlite-databases/)
- [Log SQLite updates with a trigger in C#](https://recursiveai.org/onelite/cookbook/log-sqlite-updates-with-trigger/)

## The short answer

Put the destination table after `UPDATE`, the source table after `FROM`, and the match condition in `WHERE`. The right side of `SET` can read from the matching source row. Run the statement with OneLite’s `ExecuteAsync` in C# when you want the affected-row count. `UPDATE … FROM` changes existing rows only: it doesn’t insert source rows that have no target.

The sample uses `incoming.id` as a primary key. That ensures each stock row has at most one matching incoming quantity. The same `UPDATE … FROM` SQL works with any provider backed by [SQLite 3.33.0 or later](https://www.sqlite.org/lang_update.html#update_from); for `Microsoft.Data.Sqlite` with OneLite, see the [OneLite adapter guide](https://recursiveai.org/onelite/docs/adapter/).

## How do you update a column based on a value from another table?

Think of the statement as a join followed by an assignment. Here `stock.id = incoming.id` decides which rows to update and `qty = incoming.qty` supplies the new value. The destination column on the left of `SET` is unqualified; qualify the source on the right if both tables have a column of the same name. Don’t list the target again in `FROM` unless you’re deliberately joining a table to itself under another alias.

To check the match before you write, run the equivalent `SELECT` against the sample tables just before the update:

```sql
SELECT stock.id, stock.qty, incoming.qty
FROM stock JOIN incoming ON stock.id = incoming.id
ORDER BY stock.id;
```

In the full program’s setup this returns `(1, 3, 8)` and `(3, 1, 6)`: the ID, current quantity, and proposed quantity. Check that those are exactly the rows you intend to modify. The match condition is what ties each new value to the correct target; leaving it out pairs target rows with unrelated source rows.

## How do you handle duplicate source rows in SQLite UPDATE FROM?

If several source rows match one target, the [SQLite documentation](https://www.sqlite.org/lang_update.html#update_from) says the engine uses an arbitrary match. Don’t assume it uses the last row loaded. If the source represents a single replacement value per ID, make the key unique so a duplicate fails at load time. If duplicates are valid, reduce the source to one row per ID before the update. For example, for a shipment containing multiple lines per item, sum the incoming quantities and add that total once:

```sql
UPDATE stock
SET qty = stock.qty + shipment.total
FROM (
  SELECT id, SUM(qty) AS total
  FROM incoming
  GROUP BY id
) AS shipment
WHERE stock.id = shipment.id;
```

For a replacement feed, summing is usually the wrong rule. Decide which source record wins, or reject the duplicate instead of relying on the update to decide.
