The UPDATE Query in SQLite: A Complete Guide

Data changes. Prices go up, order statuses move from “pending” to “shipped,” user profiles get edited, typos get corrected. The UPDATE statement is how you modify existing rows in a SQLite table without deleting and re-inserting them, and it’s a piece of SQL I use constantly — probably more than DELETE, and almost as often as SELECT. In this guide, I’ll walk through the full syntax, the common patterns, and the mistakes that can turn a routine update into a genuine incident.

What UPDATE Does

UPDATE modifies the values of one or more columns in existing rows that match a given condition. It doesn’t add new rows and doesn’t remove any — it only changes the data already there.

The basic syntax is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, to update a customer’s email address:

UPDATE customers
SET email = 'newemail@example.com'
WHERE customer_id = 42;

This changes the email column for exactly one row — the customer with customer_id equal to 42.

Updating Multiple Columns at Once

You can update as many columns as you need in a single UPDATE statement, separated by commas:

UPDATE customers
SET email = 'newemail@example.com',
    phone = '555-1234',
    updated_at = datetime('now')
WHERE customer_id = 42;

I do this constantly — whenever a record changes, I also update a updated_at timestamp column in the same statement, which is a genuinely useful habit for tracking data freshness and debugging later.

Updating Without a WHERE Clause

Just like DELETE, if you omit the WHERE clause, UPDATE affects every single row in the table:

UPDATE products
SET in_stock = 0;

This sets in_stock to 0 for every product in the entire table. This is sometimes exactly what you want — for instance, resetting a status flag globally before recalculating it — but it’s also a classic source of “oh no” moments if you forget the WHERE clause when you actually meant to target specific rows. I follow the same discipline here that I do with DELETE: preview the affected rows with a SELECT first if there’s any doubt.

-- Preview first
SELECT * FROM products WHERE category = 'discontinued';

-- Then update
UPDATE products SET in_stock = 0 WHERE category = 'discontinued';

Using Expressions in SET

The value you assign in a SET clause doesn’t have to be a literal — it can be any valid expression, including references to the current value of that same column or other columns in the row.

-- Give everyone a 10% raise
UPDATE employees
SET salary = salary * 1.10;

-- Increment a view counter
UPDATE articles
SET views = views + 1
WHERE article_id = 501;

-- Combine first and last name into a full name column
UPDATE users
SET full_name = first_name || ' ' || last_name;

That last example uses SQLite’s || string concatenation operator, which comes in handy constantly for building composite text values directly inside an UPDATE.

You can also use CASE expressions inside SET for conditional logic within a single update:

UPDATE employees
SET bonus = CASE
    WHEN performance_rating >= 9 THEN salary * 0.15
    WHEN performance_rating >= 7 THEN salary * 0.10
    ELSE salary * 0.05
END;

This applies different bonus percentages to every employee in a single pass, based on their performance rating, without needing separate UPDATE statements for each tier.

Updating with Conditions

Most real-world UPDATE statements target specific rows using a WHERE clause with one or more conditions, exactly like SELECT and DELETE.

-- Update a single row
UPDATE orders SET status = 'shipped' WHERE order_id = 1001;

-- Update rows matching multiple conditions
UPDATE orders
SET status = 'expired'
WHERE status = 'pending' AND order_date < '2023-01-01';

-- Update using IN
UPDATE products
SET discontinued = 1
WHERE category_id IN (5, 8, 12);

-- Update using LIKE
UPDATE customers
SET marketing_opt_in = 0
WHERE email LIKE '%@tempmail.com';

Updating Using a Subquery

Sometimes the new value you want to set isn’t a static expression, but something you need to look up from another table. SQLite supports scalar subqueries directly inside a SET clause.

UPDATE orders
SET customer_name = (
    SELECT name FROM customers WHERE customers.customer_id = orders.customer_id
)
WHERE customer_name IS NULL;

This is a common pattern when denormalizing data — copying a value from a related table into the current table to avoid a join at read time. Just be careful: the subquery must return exactly one row (or zero, which results in NULL) per row being updated, or SQLite will raise an error about the subquery returning more than one row.

You can also use a subquery inside the WHERE clause to decide which rows to update based on conditions in another table:

UPDATE employees
SET department = 'Unassigned'
WHERE department_id IN (
    SELECT department_id FROM departments WHERE closed = 1
);

The UPDATE … FROM Syntax (SQLite 3.33.0+)

Starting with SQLite version 3.33.0, SQLite added support for a more powerful UPDATE ... FROM syntax, similar to what PostgreSQL has long supported. This lets you join against another table directly within the UPDATE statement, rather than relying purely on correlated subqueries.

UPDATE orders
SET customer_name = customers.name
FROM customers
WHERE orders.customer_id = customers.customer_id;

This achieves the same denormalization goal as the subquery example above, but with cleaner, more readable join syntax — and it can be noticeably more efficient for updates that need to pull in data from multiple joined tables at once. If you’re on an older SQLite version (below 3.33.0), you’ll need to fall back to the scalar subquery approach instead, since UPDATE ... FROM simply isn’t available there.

UPDATE with LIMIT and ORDER BY — A SQLite Caveat

Just like DELETE, standard SQLite builds do not support LIMIT or ORDER BY directly on an UPDATE statement unless the library was specifically compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option — which most default distributions don’t include.

So this will fail on a standard build:

UPDATE logs
SET archived = 1
ORDER BY created_at ASC
LIMIT 100;

The portable workaround, again, is a subquery with IN:

UPDATE logs
SET archived = 1
WHERE id IN (
    SELECT id FROM logs
    WHERE archived = 0
    ORDER BY created_at ASC
    LIMIT 100
);

This marks the 100 oldest unarchived log entries as archived, and it works consistently regardless of which specific SQLite build your application is running against.

Triggers on UPDATE

SQLite supports BEFORE UPDATE and AFTER UPDATE triggers, which are useful for enforcing business rules, maintaining audit logs, or automatically updating related data whenever a row changes.

CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
WHEN old.salary IS NOT new.salary
BEGIN
    INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at)
    VALUES (old.employee_id, old.salary, new.salary, datetime('now'));
END;

Notice the OF salary clause — this restricts the trigger to only fire when the salary column specifically is part of the UPDATE statement, and the WHEN clause further restricts it to only fire when the value actually changed. This combination avoids unnecessary trigger executions on updates that don’t touch salary at all, or that “update” salary to the exact same value it already had.

Inside a trigger, old refers to the row’s values before the update, and new refers to the values after — this is genuinely one of the most useful features for building audit trails without cluttering your application code with manual logging calls.

Automatically Updating a “Last Modified” Timestamp

A pattern I use in nearly every schema I design: a trigger that automatically stamps a row with the current time whenever it’s updated, so I never have to remember to set it manually in application code.

CREATE TRIGGER set_updated_at
AFTER UPDATE ON customers
BEGIN
    UPDATE customers
    SET updated_at = datetime('now')
    WHERE customer_id = new.customer_id;
END;

This way, no matter which part of the application performs an update, the updated_at column is always kept accurate automatically.

Wrapping UPDATE in a Transaction

Because UPDATE can silently affect far more rows than intended if a WHERE clause is wrong, I recommend wrapping bulk or conditional updates in a transaction so you can verify the results before committing.

BEGIN TRANSACTION;

UPDATE orders
SET status = 'expired'
WHERE status = 'pending' AND order_date < '2023-01-01';

SELECT changes();  -- confirm the row count matches expectations

COMMIT;
-- or ROLLBACK; if the number looks wrong

SELECT changes(); returns the number of rows affected by the most recent INSERT, UPDATE, or DELETE, and it’s an easy sanity check before finalizing a transaction — especially for updates running against production data.

Common Mistakes to Avoid

1. Forgetting the WHERE clause, updating every row in a table when only a subset was intended.
2. Assuming LIMIT and ORDER BY work directly on UPDATE. They don’t on standard SQLite builds — use the subquery pattern instead.
3. Writing a subquery in SET that can return more than one row, causing a runtime error. Make sure subqueries used as scalar values are properly scoped to return at most one row.
4. Not accounting for trigger side effects. If a table has AFTER UPDATE triggers, a single UPDATE statement can cascade into other changes you might not be expecting — always review your triggers when debugging unexpected data changes.
5. Comparing old and new values incorrectly in a trigger’s WHEN clause. Use IS NOT rather than != when comparing values that might be NULL, since != against NULL always evaluates to NULL (neither true nor false), which can cause the trigger condition to silently fail to match.

Best Practices

• Always preview the affected rows with a SELECT before running an UPDATE with a broad or conditional WHERE clause.
• Use expressions and CASE statements inside SET to consolidate multiple conditional updates into a single statement.
• Prefer UPDATE ... FROM (on SQLite 3.33.0+) over correlated subqueries when updating based on data from another table — it’s clearer and often faster.
• Wrap bulk updates in explicit transactions, and check SELECT changes(); before committing.
• Use triggers to automatically maintain timestamps and audit trails, rather than relying on every part of your application to remember to do it manually.
• Enable PRAGMA foreign_keys = ON; if your updates need to respect foreign key constraints tied to related tables.

UPDATE is one of the statements you’ll write constantly in any real application, and getting comfortable with its full range — expressions, subqueries, the newer FROM syntax, and its interaction with triggers — will make your data-modification code both more powerful and considerably safer.

Frequently Asked Questions

Can UPDATE change a primary key value? Yes, SQLite allows updating a primary key column, as long as the new value doesn’t violate uniqueness. However, if other tables reference that primary key via foreign keys, and PRAGMA foreign_keys = ON; is set, SQLite will either block the update or cascade it to related tables, depending on whether ON UPDATE CASCADE was defined in the foreign key. I generally avoid designing schemas where primary keys need to change at all — it’s usually a sign that a separate, stable surrogate key would be a better fit.

What happens if my UPDATE’s WHERE clause matches zero rows? Nothing happens — SQLite doesn’t raise an error, it simply reports zero rows affected. You can check this with SELECT changes(); immediately after running the UPDATE, which is a useful sanity check when you expect a certain number of rows to be modified and want to confirm that expectation programmatically.

Can I update a column based on a value from a different row in the same table? Yes, typically using a correlated subquery or the UPDATE ... FROM syntax (on SQLite 3.33.0+).

UPDATE employees
SET manager_name = (
    SELECT name FROM employees AS managers WHERE managers.employee_id = employees.manager_id
);

Does UPDATE fire triggers even if the new value is identical to the old value? By default, yes — an UPDATE statement fires any relevant AFTER UPDATE or BEFORE UPDATE triggers regardless of whether the new value actually differs from the old one, unless the trigger itself includes a WHEN clause that explicitly checks for a change (as shown in the salary-history trigger example earlier in this guide).

UPDATE Performance Considerations

Just like SELECT and DELETE, an UPDATE statement’s WHERE clause benefits significantly from indexes on the filtered columns. Without an index, SQLite has to scan the entire table to identify which rows match your condition before it can apply the changes.

CREATE INDEX idx_orders_status ON orders(status);

UPDATE orders SET priority = 'high' WHERE status = 'pending' AND order_date < '2024-06-01';

Beyond indexing the WHERE clause, it’s also worth being aware that updating an indexed column itself carries additional overhead, since SQLite has to update the index structure in addition to the underlying table row. If you’re doing extremely high-volume bulk updates on a heavily-indexed column, that overhead is worth factoring into your performance expectations, and in some genuinely large-scale batch-update scenarios, it can even be faster to drop the index, perform the bulk update, and then recreate the index afterward.

Batching Large Updates

Similar to the batching pattern I described for DELETE, extremely large UPDATE operations against live, actively-used databases benefit from being split into smaller batches rather than run as one enormous statement that holds a write lock for an extended period.

UPDATE orders
SET archived = 1
WHERE id IN (
    SELECT id FROM orders
    WHERE archived = 0 AND order_date < '2022-01-01'
    LIMIT 5000
);

Running this repeatedly in a loop from application code, checking SELECT changes(); to know when no more rows remain to update, allows other read and write operations to interleave between batches rather than being blocked for the full duration of a single massive update.

Common Real-World UPDATE Scenarios

Here are a few more patterns I use regularly that go beyond the basics already covered:

-- Normalize inconsistent casing in existing data
UPDATE customers SET email = LOWER(email);

-- Recalculate a derived/denormalized column after related data changed
UPDATE order_summaries
SET total_items = (SELECT COUNT(*) FROM order_items WHERE order_items.order_id = order_summaries.order_id);

-- Apply a bulk status transition based on a business rule
UPDATE subscriptions
SET status = 'expired'
WHERE status = 'active' AND renewal_date < date('now');

-- Clean up whitespace issues from imported data
UPDATE products SET product_name = TRIM(product_name);

That normalization pattern — lowercasing emails, trimming whitespace — is something I run almost every time I inherit a dataset that was imported from an external source or entered manually over time by multiple people, since inconsistent formatting in text fields is one of the most common and most easily fixable data-quality issues you’ll encounter.

UPDATE and Concurrent Access

If your SQLite database is accessed by multiple processes or threads simultaneously, it’s worth understanding that SQLite uses file-level locking during write operations like UPDATE. By default, SQLite allows only one writer at a time, and other write attempts will either wait or fail with a “database is locked” error, depending on your configured busy timeout. Enabling Write-Ahead Logging mode (PRAGMA journal_mode = WAL;) significantly improves concurrent read/write performance, allowing readers to continue working while a write transaction like an UPDATE is in progress, though it still only permits a single writer at any given moment. For applications with meaningful concurrent write activity, understanding this locking behavior is essential to avoiding unexpected “database locked” errors during routine UPDATE operations.