SQLite UPDATE: Change Rows, Multiple Columns and UPDATE FROM (Tested)

Ninad Pathak

October 7, 2026

An UPDATE without a WHERE rewrites every row in the table, and SQLite won’t ask first. There are smaller traps too, like SQLite rejecting MySQL’s UPDATE … JOIN. In this article I’ll walk you through every form of UPDATE with real output.

TLDR

Change rows with UPDATE table SET column = value WHERE condition;.
Set several columns at once by separating them with commas in SET.

Without a WHERE clause, UPDATE changes every row in the table.
To update from another table, use UPDATE ... FROM (SQLite 3.33.0 and later). SQLite doesn’t accept UPDATE ... JOIN.
RETURNING shows the changed rows, and changes() counts them.

What is the SQLite UPDATE statement?

UPDATE changes values in rows that already exist. SQLite’s documentation puts it this way: “If the UPDATE statement does not have a WHERE clause, all rows in the table are modified by the UPDATE. Otherwise, the UPDATE affects only those rows for which the WHERE clause boolean expression is true” (SQLite UPDATE).

To add new rows, use INSERT. To insert a row or update it if it exists, use an upsert.

SQLite UPDATE syntax

UPDATE table_name
SET column1 = expression1,
    column2 = expression2
[FROM other_table]
[WHERE condition]
[RETURNING columns];

Part	What it does
`SET`	The columns to change and their new values. A value can be any expression, including the column’s own value
`FROM`	Joins other tables to pick rows and compute values (3.33.0 and later)
`WHERE`	Which rows change. Leave it out and every row changes
`RETURNING`	Returns columns from the changed rows (RETURNING)
`OR IGNORE`, `OR REPLACE`	What to do when a change breaks a constraint (conflict clause)

How to update rows in SQLite

To show you each form, I will use a small products table.

Step 1: Create the table

sqlite> CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, stock INTEGER NOT NULL, category TEXT);
sqlite> INSERT INTO products (name, price, stock, category) VALUES ('Pen', 3.0, 120, 'office'), ('Notebook', 5.0, 8, 'office'), ('Mug', 9.0, 0, 'kitchen'), ('Lamp', 25.0, 4, 'home');
sqlite> SELECT * FROM products;
id  name      price  stock  category
--  --------  -----  -----  --------
1   Pen       3.0    120    office
2   Notebook  5.0    8      office
3   Mug       9.0    0      kitchen
4   Lamp      25.0   4      home

Step 2: Update one column in one row

The WHERE clause picks the row, and SET gives the new value:

sqlite> UPDATE products SET price = 2.5 WHERE name = 'Pen';
sqlite> SELECT name, price FROM products WHERE name = 'Pen';
name  price
----  -----
Pen   2.5

Step 3: Update multiple columns

Separate the assignments with commas:

sqlite> UPDATE products SET price = 22.0, stock = 10 WHERE name = 'Lamp';
sqlite> SELECT name, price, stock FROM products WHERE name = 'Lamp';
name  price  stock
----  -----  -----
Lamp  22.0   10

Examples

The examples continue with the same table, so each one starts from the previous result.

Update with an expression

The new value can use the old one. This raises office prices by 10%:

sqlite> UPDATE products SET price = round(price * 1.10, 2) WHERE category = 'office';
sqlite> SELECT name, price FROM products WHERE category = 'office';
name      price
--------  -----
Pen       2.75
Notebook  5.5

Combine conditions with AND and OR

WHERE accepts any condition a SELECT would (SQLite WHERE):

sqlite> UPDATE products SET category = 'clearance' WHERE stock < 5 AND category <> 'office';
sqlite> SELECT name, stock, category FROM products;
name      stock  category
--------  -----  ---------
Pen       120    office
Notebook  8      office
Mug       0      clearance
Lamp      10     home

Check how many rows changed

changes() returns the number of rows the last statement changed. Zero means the WHERE matched nothing, which isn’t an error:

sqlite> UPDATE products SET stock = stock + 0 WHERE price > 100; SELECT changes();
changes()
---------
0

See the changed rows with RETURNING

sqlite> UPDATE products SET stock = stock - 1 WHERE name = 'Pen' RETURNING name, stock;
name  stock
----  -----
Pen   119

Update from another table with a subquery

A correlated subquery looks up each row’s new value. The second subquery limits the update to rows that have a match:

sqlite> CREATE TABLE supplier_prices (product TEXT PRIMARY KEY, new_price REAL);
sqlite> INSERT INTO supplier_prices VALUES ('Mug', 7.5), ('Lamp', 19.99);
sqlite> UPDATE products SET price = (SELECT new_price FROM supplier_prices WHERE product = products.name) WHERE name IN (SELECT product FROM supplier_prices);
sqlite> SELECT name, price FROM products;
name      price
--------  -----
Pen       2.75
Notebook  5.5
Mug       7.5
Lamp      19.99

Update from another table with UPDATE FROM

UPDATE ... FROM joins the other table directly. The docs say it’s “supported beginning in SQLite version 3.33.0 (2020-08-14)” and that SQLite’s version “strives to be compatible with PostgreSQL” (SQLite UPDATE FROM):

sqlite> UPDATE supplier_prices SET new_price = new_price - 1;
sqlite> UPDATE products SET price = sp.new_price FROM supplier_prices AS sp WHERE sp.product = products.name;
sqlite> SELECT name, price FROM products;
name      price
--------  -----
Pen       2.75
Notebook  5.5
Mug       6.5
Lamp      18.99

MySQL’s UPDATE ... JOIN form isn’t accepted:

sqlite> UPDATE products JOIN supplier_prices ON supplier_prices.product = products.name SET price = new_price;
Error: in prepare, near "JOIN": syntax error
  UPDATE products JOIN supplier_prices ON supplier_prices.product = products.nam
                  ^--- error here

SQLite update from another table covers both forms in more depth.

Update rows from Python

Pass values as ? parameters. executemany() runs the same UPDATE for each pair of values, and rowcount adds up the changed rows:

import sqlite3

con = sqlite3.connect("t.db")
price_changes = [(3.25, "Pen"), (5.5, "Notebook")]
with con:  # commits on success
    cur = con.executemany("UPDATE products SET price = ? WHERE name = ?", price_changes)
print("rows changed:", cur.rowcount)
for row in con.execute("SELECT name, price FROM products WHERE name IN ('Pen', 'Notebook') ORDER BY name"):
    print(row)
con.close()

$ python3 update_py.py
rows changed: 2
('Notebook', 5.5)
('Pen', 3.25)

Edge cases

Each of these is an UPDATE that changes more than you meant, or fails.

UPDATE without WHERE changes every row

I ran this on a copy of the table. All four rows changed:

sqlite> CREATE TABLE products_copy AS SELECT * FROM products;
sqlite> UPDATE products_copy SET stock = 0; SELECT changes();
changes()
---------
4

Run the same WHERE as a SELECT first to see which rows will change.

UPDATE with ORDER BY and LIMIT

ORDER BY and LIMIT on an UPDATE only work when SQLite is built with SQLITE_ENABLE_UPDATE_DELETE_LIMIT (SQLite UPDATE). The macOS sqlite3 shell used here has it:

sqlite> UPDATE products SET stock = 0 ORDER BY price DESC LIMIT 1;
sqlite> SELECT name, price, stock FROM products ORDER BY price DESC;
name      price  stock
--------  -----  -----
Lamp      18.99  0
Mug       6.5    0
Notebook  5.5    8
Pen       3.25   119
sqlite> SELECT compile_options FROM pragma_compile_options WHERE compile_options LIKE '%UPDATE_DELETE_LIMIT%';
compile_options
--------------------------
ENABLE_UPDATE_DELETE_LIMIT

Python’s sqlite3 module from Homebrew doesn’t:

$ python3.13 limit_py.py
SQLite 3.53.4
OperationalError: near "ORDER": syntax error

A subquery with LIMIT works on every build:

sqlite> UPDATE products SET stock = 0 WHERE id IN (SELECT id FROM products ORDER BY price ASC LIMIT 1) RETURNING name, stock;
name  stock
----  -----
Pen   0

Constraint errors

An UPDATE that breaks a constraint fails, and no row changes:

sqlite> UPDATE products SET name = NULL WHERE id = 1;
Error: stepping, NOT NULL constraint failed: products.name (19)

SELECT … FOR UPDATE isn’t supported

SQLite locks the whole database file for writes, so it has no row locks to take:

sqlite> SELECT * FROM products WHERE id = 1 FOR UPDATE;
Error: in prepare, near "FOR": syntax error
  SELECT * FROM products WHERE id = 1 FOR UPDATE;
                        error here ---^

Wrap the read and the update in one transaction with BEGIN IMMEDIATE instead. See database is locked.

Views can’t be updated directly

sqlite> CREATE VIEW cheap AS SELECT * FROM products WHERE price < 5;
sqlite> UPDATE cheap SET price = 1;
Error: in prepare, cannot modify cheap because it is a view

Update the underlying table, or add an INSTEAD OF UPDATE trigger on the view (SQLite CREATE TRIGGER).

Conclusion

Write the WHERE clause first, and test it as a SELECT before you run the UPDATE. Use UPDATE ... FROM or a subquery to pull values from another table. Then check what changed with RETURNING or changes(). To change a column’s definition rather than its values, see SQLite ALTER TABLE ADD COLUMN.