Copying values from one table into another, like a customer’s city onto each of their orders, takes one statement in SQLite, not a loop in app code. In this article I’ll walk you through UPDATE … FROM and the older subquery method, including the traps that quietly write NULLs or wrong values.
TLDR
- On SQLite 3.33.0 and later, use
UPDATE target SET col = src.col FROM src WHERE src.id = target.src_id;. - On older versions, use a correlated subquery in
SET, and addWHERE EXISTS (…)so rows without a match keep their value. - Don’t repeat the target table in the
FROMclause. That turns the update into a cross join and writes the wrong values. - To update several columns from the same row, list each one in
SET, or use a row value:SET (a, b) = (SELECT …).
What does “update from another table” mean?
It means changing rows in one table (the target) using values that live in a second table (the source), matched on a key such as customer_id. SQLite has two ways to write it:
UPDATE … FROM, a join between the target and the source. The SQLite UPDATE documentation says “UPDATE-FROM is supported beginning in SQLite version 3.33.0 (2020-08-14)”, and that SQLite’s version “strives to be compatible with PostgreSQL”.- A subquery in
SET, which runs once per target row and works on every SQLite version. This is the “update from select” form.
SQLite UPDATE FROM syntax
UPDATE target_table
SET column1 = source.column1,
column2 = source.column2
FROM source_table AS source
WHERE source.key = target_table.key;The subquery form for older versions:
UPDATE target_table
SET column1 = (SELECT column1 FROM source_table WHERE source_table.key = target_table.key)
WHERE EXISTS (SELECT 1 FROM source_table WHERE source_table.key = target_table.key);How to update a table from another table
To show you each method, I will use a customers table and an orders table. Order 5 has no customer, which matters for the traps later on:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
city TEXT
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date TEXT,
total REAL,
shipping_city TEXT
);
INSERT INTO customers (name, email, city) VALUES
('ABC Corp', 'contact@abccorp.com', 'New York'),
('XYZ Inc', 'info@xyzinc.com', 'San Francisco'),
('Acme Co', 'sales@acmeco.com', 'Chicago');
INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2026-01-10', 1250.00),
(2, '2026-02-15', 800.00),
(1, '2026-03-05', 2500.00),
(3, '2026-04-22', 1800.00),
(NULL, '2026-05-01', 99.00);Every example below starts from a fresh copy of these two tables.
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 NULL
2 2 800.0 NULL
3 1 2500.0 NULL
4 3 1800.0 NULL
5 NULL 99.0 NULLStep 1: Join the source table with UPDATE … FROM
Name the source table in FROM and match it to the target in WHERE. Order 5 has no matching customer, so it isn’t touched:
sqlite> UPDATE orders SET shipping_city = c.city FROM customers AS c WHERE c.customer_id = orders.customer_id;
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 New York
2 2 800.0 San Francisco
3 1 2500.0 New York
4 3 1800.0 Chicago
5 NULL 99.0 NULLStep 2: Use a subquery on SQLite older than 3.33.0
A correlated subquery in SET looks up the value for each order. There’s a catch: rows with no match get NULL, even if they had a value before. Here every shipping_city starts as 'unknown', and order 5 loses it:
sqlite> UPDATE orders SET shipping_city = 'unknown';
sqlite> UPDATE orders SET shipping_city = (SELECT city FROM customers WHERE customers.customer_id = orders.customer_id);
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 New York
2 2 800.0 San Francisco
3 1 2500.0 New York
4 3 1800.0 Chicago
5 NULL 99.0 NULLAdd a WHERE EXISTS with the same condition, so only rows with a match are updated:
sqlite> UPDATE orders SET shipping_city = 'unknown';
sqlite> UPDATE orders SET shipping_city = (SELECT city FROM customers WHERE customers.customer_id = orders.customer_id) WHERE EXISTS (SELECT 1 FROM customers WHERE customers.customer_id = orders.customer_id);
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 New York
2 2 800.0 San Francisco
3 1 2500.0 New York
4 3 1800.0 Chicago
5 NULL 99.0 unknownExamples
Each of these starts from the same two tables.
Update multiple columns from another table
With UPDATE … FROM, list each column in SET. They all come from the same matched row:
sqlite> ALTER TABLE orders ADD COLUMN customer_name TEXT;
sqlite> ALTER TABLE orders ADD COLUMN customer_email TEXT;
sqlite> UPDATE orders SET customer_name = c.name, customer_email = c.email FROM customers AS c WHERE c.customer_id = orders.customer_id;
sqlite> SELECT order_id, customer_name, customer_email FROM orders;
order_id customer_name customer_email
-------- ------------- -------------------
1 ABC Corp contact@abccorp.com
2 XYZ Inc info@xyzinc.com
3 ABC Corp contact@abccorp.com
4 Acme Co sales@acmeco.com
5 NULL NULLWith a subquery, a row value sets several columns from one SELECT. This also applies a 10% discount to customer 1’s orders:
sqlite> UPDATE orders SET (shipping_city, total) = (SELECT city, orders.total * 0.9 FROM customers WHERE customers.customer_id = orders.customer_id) WHERE customer_id = 1;
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1125.0 New York
2 2 800.0 NULL
3 1 2250.0 New York
4 3 1800.0 NULL
5 NULL 99.0 NULLUpdate with totals from another table
Join against a grouped subquery to write an aggregate, here each customer’s lifetime order total:
sqlite> ALTER TABLE customers ADD COLUMN lifetime_total REAL DEFAULT 0;
sqlite> UPDATE customers SET lifetime_total = t.sum_total FROM (SELECT customer_id, sum(total) AS sum_total FROM orders GROUP BY customer_id) AS t WHERE t.customer_id = customers.customer_id;
sqlite> SELECT customer_id, name, lifetime_total FROM customers;
customer_id name lifetime_total
----------- -------- --------------
1 ABC Corp 3750.0
2 XYZ Inc 800.0
3 Acme Co 1800.0Update only some rows, with CASE
Extra conditions go in the WHERE clause, next to the join condition. A CASE expression picks the value per row:
sqlite> UPDATE orders SET shipping_city = CASE WHEN c.city = 'New York' THEN 'NYC hub' ELSE c.city END FROM customers AS c WHERE c.customer_id = orders.customer_id AND orders.order_date < '2026-04-01';
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 NYC hub
2 2 800.0 San Francisco
3 1 2500.0 NYC hub
4 3 1800.0 NULL
5 NULL 99.0 NULLSee which rows changed with RETURNING
RETURNING lists the updated rows. Order 5 isn’t in the list:
sqlite> UPDATE orders SET shipping_city = c.city FROM customers AS c WHERE c.customer_id = orders.customer_id RETURNING order_id, shipping_city;
order_id shipping_city
-------- -------------
1 New York
2 San Francisco
3 New York
4 ChicagoEdge cases
These are the mistakes that run without an error but leave the wrong data behind, plus syntax that depends on how SQLite was built.
Repeating the target table in FROM
This is the join form that old tutorials show, including the version of this guide that used to live at /sqlite-update-from-select/. It looks like it matches orders to customers, but the orders AS o in FROM is a second copy of the table. Nothing links it to the row being updated, so every order gets the same city:
sqlite> UPDATE orders SET shipping_city = c.city FROM orders AS o JOIN customers AS c ON o.customer_id = c.customer_id;
sqlite> SELECT order_id, customer_id, total, shipping_city FROM orders;
order_id customer_id total shipping_city
-------- ----------- ------ -------------
1 1 1250.0 Chicago
2 2 800.0 Chicago
3 1 2500.0 Chicago
4 3 1800.0 Chicago
5 NULL 99.0 ChicagoSQLite’s documentation says the target table should not be in the FROM clause “unless the intent is to do a self-join”. When one target row matches several source rows, “the output row selected is arbitrary”. Make sure the join matches each target row once.
ORDER BY and LIMIT in UPDATE
UPDATE … ORDER BY … LIMIT only works when SQLite was compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT (SQLite UPDATE). Check your build first. The sqlite3 shell on this Mac has it, so this discounts customer 1’s latest order only:
sqlite> SELECT sqlite_compileoption_used('ENABLE_UPDATE_DELETE_LIMIT') AS limit_enabled;
limit_enabled
-------------
1
sqlite> UPDATE orders SET total = total * 0.9 WHERE customer_id = 1 ORDER BY order_date DESC LIMIT 1;
sqlite> SELECT order_id, order_date, total FROM orders WHERE customer_id = 1;
order_id order_date total
-------- ---------- ------
1 2026-01-10 1250.0
3 2026-03-05 2250.0SQL Server and MySQL write it differently
The documentation notes that SQL Server’s UPDATE FROM needs the target table inside the FROM clause, and MySQL uses UPDATE … JOIN. Don’t copy those forms into SQLite.
Conclusion
Use UPDATE … FROM on SQLite 3.33.0 and later. It’s the clearest form, and rows without a match are left alone. On older versions, use a correlated subquery with a matching WHERE EXISTS. In both, keep the target table out of FROM.
Further reading
- SQLite UPDATE query
- SQLite WHERE clause
- SQLite SELECT statement
- SQLite CREATE TABLE
- SQLite ORDER BY
- SQLite LIMIT
- SQLite RETURNING clause
- SQLite docs: UPDATE
Tested with SQLite 3.51.0 on 7 October 2026.