Data Changes

Update Rows

Changing Existing Data

UPDATE changes columns in rows that match a condition.

Program

Play the script to mark one item as stocked.

update_rows.sql
Replay: real traced execution (multi-file project)
CREATE TABLE inventory (sku TEXT, stock INTEGER);
INSERT INTO inventory VALUES ('pen', 0), ('book', 3);
UPDATE inventory SET stock = 5 WHERE sku = 'pen';
SELECT * FROM inventory ORDER BY sku;

  1. inventory ← 0 rows

    1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 0), ('book', 3);
    values this step0 rowsinventory

  2. inventory ← 2 rows

    1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 0), ('book', 3);3UPDATE inventory SET stock = 5 WHERE sku = 'pen';
    values this step2 rowsinventory

  3. inventory ← 2 rows

    2INSERT INTO inventory VALUES ('pen', 0), ('book', 3);3UPDATE inventory SET stock = 5 WHERE sku = 'pen';4SELECT * FROM inventory ORDER BY sku;
    values this step2 rowsinventory

  4. result ← 2 rows

    3UPDATE inventory SET stock = 5 WHERE sku = 'pen';4SELECT * FROM inventory ORDER BY sku;
    values this step2 rowsresult

Follow the Update

  1. Before the update, ordered by sku, book has stock 3.
  2. The pen row starts with stock 0.
  3. UPDATE inventory SET stock = 5 WHERE sku = 'pen' matches only pen.
  4. The final table keeps book at 3 and changes pen to 5. | sku | before stock | after stock | | --- | --- | --- | | book | 3 | 3 | | pen | 0 | 5 |
UPDATE `UPDATE inventory SET stock = 5` changes a column.
WHERE The `WHERE` clause limits which rows change.
state change The table snapshot shows before and after state.

Exercise: update_rows.sql

Reproduce the final stock values book=3 and pen=5, then identify which row changed and which row stayed the same.