SQLite UPDATE: WHERE, Multiple Columns, and UPDATE FROM

UPDATE Changes Existing Rows

INSERT adds new rows. UPDATE modifies rows that already exist. The shape is short and worth memorizing:

UPDATE table_name
SET column = value
WHERE condition;

A working example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    status TEXT DEFAULT 'active'
);

INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@old.com'),
    ('Boris', 'boris@old.com'),
    ('Rosa', 'rosa@old.com');

UPDATE users
SET email = 'ada@new.com'
WHERE id = 1;

SELECT * FROM users;

SET says what changes. WHERE says which rows. The rest of the table is left alone.

The WHERE Clause Is Not Optional in Practice

Technically, WHERE is optional. Practically, leaving it off is how junior engineers ruin their afternoon:

UPDATE users SET status = 'inactive';
-- every single user is now inactive

No filter means every row matches. SQLite will quietly oblige. Always write the WHERE first, then the SET — that habit alone prevents a lot of accidents.

If you're not sure your WHERE is right, run the same condition as a SELECT first:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
INSERT INTO users VALUES (1, 'Ada', 'active'), (2, 'Boris', 'active'), (3, 'Rosa', 'pending');

-- Preview which rows would be touched.
SELECT id, name, status FROM users WHERE status = 'pending';

-- Then run the update with the exact same WHERE.
UPDATE users SET status = 'active' WHERE status = 'pending';

SELECT * FROM users;

Same condition, two statements. The SELECT is your dry run.

Updating Multiple Columns

Comma-separate the assignments inside a single SET. One SET, many columns:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products VALUES (1, 'Widget', 9.99, 100);

UPDATE products
SET price = 12.99,
    stock = 75,
    name = 'Premium Widget'
WHERE id = 1;

SELECT * FROM products;

One round-trip to the database, one row changed, three columns updated. Don't write three separate UPDATE statements when one will do.

Expressions on the Right of =

The value after = doesn't have to be a literal. It can be any expression — including one that references the column's current value:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);
INSERT INTO products VALUES
    (1, 'Widget', 10.00, 50),
    (2, 'Gadget', 20.00, 30),
    (3, 'Gizmo', 15.00, 80);

-- Raise prices by 10%.
UPDATE products
SET price = price * 1.10;

-- Decrement stock for one row.
UPDATE products
SET stock = stock - 1
WHERE id = 2;

SELECT * FROM products;

price * 1.10 reads the existing price, multiplies, and writes the result back. SQLite evaluates the right side using the row's current values before any of this statement's assignments take effect, so you can reference several columns safely:

UPDATE products SET price = price * 1.10, stock = stock + price;
-- 'price' on the right side here is the OLD price, not the just-updated one.

UPDATE ... FROM: Pulling Values From Another Table

Since SQLite 3.33, UPDATE supports a FROM clause for cross-table updates. This is the cleanest way to sync data between tables:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, total_spent REAL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);

INSERT INTO customers (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO orders (customer_id, amount) VALUES
    (1, 50.00), (1, 30.00),
    (2, 100.00),
    (3, 25.00), (3, 75.00);

UPDATE customers
SET total_spent = totals.sum_amount
FROM (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM orders
    GROUP BY customer_id
) AS totals
WHERE customers.id = totals.customer_id;

SELECT * FROM customers;

The subquery computes per-customer totals; the outer UPDATE joins those results back into customers on id. Without UPDATE ... FROM you'd be writing a correlated subquery for every column — much noisier.

A few rules to keep in mind:

• The target table goes after UPDATE, not in the FROM list.
• The WHERE clause does the join — there's no ON keyword here.
• If the join could match more than one row in FROM, the result is unspecified. Make sure your join keys produce at most one match per target row.

RETURNING: See What Changed

SQLite (3.35+) lets UPDATE return the modified rows in the same statement. Handy when your application needs the post-update values without a follow-up SELECT:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Widget', 10.00), (2, 'Gadget', 20.00);

UPDATE products
SET price = price * 0.9
WHERE price > 15
RETURNING id, name, price;

You get back the rows that were actually touched, with their new values. Saves a round-trip and removes a class of race conditions in concurrent code. There's a whole page on RETURNING later in this chapter.

UPDATE OR REPLACE: Handling Constraint Conflicts

If your update would violate a UNIQUE constraint, the default behavior is to abort the statement with an error. The OR clause lets you pick a different policy:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT
);

INSERT INTO users VALUES
    (1, 'ada@x.com', 'Ada'),
    (2, 'boris@x.com', 'Boris');

-- This would fail with a UNIQUE constraint violation:
-- UPDATE users SET email = 'boris@x.com' WHERE id = 1;

-- OR REPLACE deletes the conflicting row, then performs the update.
UPDATE OR REPLACE users
SET email = 'boris@x.com'
WHERE id = 1;

SELECT * FROM users;

The options are OR ABORT (default), OR REPLACE, OR IGNORE, OR FAIL, and OR ROLLBACK. REPLACE is the dangerous one — it deletes the conflicting row, which can cascade through foreign keys. Use it only when you genuinely mean "if there's already a row with this unique value, throw it away."

For most upsert-style work, the dedicated INSERT ... ON CONFLICT syntax is clearer. There's a page on that.

Wrap Risky Updates in a Transaction

When you're modifying a lot of rows or running multiple UPDATE statements that need to succeed together, wrap them in a transaction. If anything goes wrong, you can roll back to the state before:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Ada', 100.00), (2, 'Boris', 50.00);

BEGIN;
    UPDATE accounts SET balance = balance - 25 WHERE id = 1;
    UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

SELECT * FROM accounts;

If the second statement fails (say, a constraint kicks in), ROLLBACK undoes the first. Without a transaction, you'd be left with a half-finished transfer — Ada down 25, Boris unchanged. Transactions get their own chapter later; just know they exist and that bulk updates almost always belong inside one.

Common Pitfalls

A short list of things that bite people:

• Forgetting WHERE — updates every row. Read your statement out loud before running it.
• Wrong operator in WHERE — WHERE status = NULL matches nothing. Use IS NULL. We'll cover this in the operators page.
• Updating with a subquery that returns more than one row when you expect one. Use LIMIT 1 or aggregate the subquery, or you'll get errors or surprising results.
• Confusing UPDATE OR REPLACE with UPSERT. OR REPLACE deletes conflicting rows. INSERT ... ON CONFLICT DO UPDATE modifies them in place. Different operations.

Next: DELETE

UPDATE modifies rows; DELETE removes them. The same WHERE discipline applies — and the same "run a SELECT first" habit will save you from the same kinds of disasters. That's the next page.