SQLite UPDATE

The UPDATE statement modifies existing data in a table. Use it whenever a value in your database needs to change — a price adjustment, a status change, or a correction. Always pair UPDATE with a WHERE clause to target specific rows; without it, every row in the table changes.

Sample Table

inventory
────────────────────────────────────────────────────
id │ name          │ price  │ stock │ category
───┼───────────────┼────────┼───────┼─────────────
1  │ Notebook      │ 2.99   │ 50    │ Stationery
2  │ Pen           │ 0.99   │ 200   │ Stationery
3  │ Backpack      │ 24.99  │ 15    │ Bags
4  │ Water Bottle  │ 8.50   │ 30    │ Accessories
5  │ Eraser        │ 0.59   │ 0     │ Stationery

Basic UPDATE Syntax

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

Update One Column

-- Raise Notebook price to 3.49
UPDATE inventory
SET price = 3.49
WHERE id = 1;

-- After update:
id │ name     │ price  │ ...
───┼──────────┼────────┼────
1  │ Notebook │ 3.49   │ ...   ← changed
2  │ Pen      │ 0.99   │ ...   ← unchanged

Update Multiple Columns at Once

-- Backpack gets a new price and restocked
UPDATE inventory
SET price = 22.99,
    stock = 40
WHERE id = 3;

id │ name     │ price  │ stock │ ...
───┼──────────┼────────┼───────┼────
3  │ Backpack │ 22.99  │ 40    │ ...

Update Using the Existing Value

You can reference the current value of a column in the update expression. This avoids needing to read the value first.

-- Apply 10% price increase to all Stationery items
UPDATE inventory
SET price = price * 1.10
WHERE category = 'Stationery';

BEFORE                     AFTER
────────────────────────   ────────────────────────
Notebook  │ 2.99           Notebook  │ 3.289
Pen       │ 0.99           Pen       │ 1.089
Eraser    │ 0.59           Eraser    │ 0.649

-- Add 10 units to Eraser stock
UPDATE inventory
SET stock = stock + 10
WHERE name = 'Eraser';

Update All Rows (No WHERE)

Omitting WHERE updates every row. This is rarely what you want but occasionally valid — for example, resetting all prices during a sale.

-- Apply a 5% discount to EVERY product (intentional)
UPDATE inventory
SET price = price * 0.95;

-- DANGER: this zeros out ALL stock counts
UPDATE inventory SET stock = 0;

Update with Subquery

A subquery inside UPDATE lets you set a value based on data from another table.

-- Set stock to 0 for products in the 'discontinued' table
UPDATE inventory
SET stock = 0
WHERE id IN (SELECT product_id FROM discontinued);

Checking Rows Affected

In the SQLite shell, you can check how many rows an UPDATE changed using changes().

UPDATE inventory
SET stock = stock + 5
WHERE category = 'Stationery';

SELECT changes();
-- Returns: 3   (three stationery rows updated)

Safe UPDATE Workflow

Step 1: SELECT the rows you intend to update
────────────────────────────────────────────
SELECT id, name, price FROM inventory
WHERE category = 'Stationery';

Step 2: Confirm these are the right rows

Step 3: Run the UPDATE using the same WHERE clause
────────────────────────────────────────────
UPDATE inventory
SET price = price * 1.10
WHERE category = 'Stationery';

Step 4: Verify with SELECT again
────────────────────────────────────────────
SELECT id, name, price FROM inventory
WHERE category = 'Stationery';

UPDATE vs INSERT vs DELETE

OPERATION     EFFECT
────────────────────────────────────────────────
INSERT        Adds new rows
UPDATE        Changes values in existing rows
DELETE        Removes existing rows entirely

Using Transactions with UPDATE

Wrap critical updates in a transaction. If something goes wrong, roll back to the original state.

BEGIN;

UPDATE inventory SET price = price * 1.20
WHERE category = 'Bags';

-- Verify the result looks correct
SELECT * FROM inventory WHERE category = 'Bags';

-- If good:
COMMIT;

-- If wrong:
ROLLBACK;

Key Takeaways

• UPDATE table SET col = val WHERE condition changes matching rows
• Always use WHERE to target specific rows — omitting it updates every row
• You can update multiple columns in one statement using comma-separated assignments
• Use the existing column value in the expression: SET price = price * 1.10
• Use SELECT first to preview which rows your WHERE clause affects