Recursive AI

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.

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

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
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, then dotnet run.

The full program

C#
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

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; for Microsoft.Data.Sqlite with OneLite, see the OneLite adapter guide.

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 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.

All onelite recipes