Use UPDATE … FROM to copy values into existing rows by a shared key, such as a stock item ID.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
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.
1: 8 2: 5
Unzip the example, then dotnet run.
The full program
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);
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:
UPDATE stock AS target SET qty = target.qty + source.qty FROM incoming AS source WHERE target.id = source.id;
Rows updated: 2 1: 11 2: 5 3: 7
Related recipes
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:
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:
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.