Change the Requested Jobs Without Rewriting the Rest

New inspections arrive, existing ones change, and cancelled drafts leave the queue. Give each write a precise target and preserve newer information.

14 sections · 4 questions · 1 coding challenge

1 / 14

The arrow keys move between sections too.

Describing a Set of Rows to Change

BOTIE

The early SEQUEL work described composable operations over tabular data. That set-oriented approach also means an update can target many records at once. The useful question becomes which rows satisfy the write’s predicate.

Modern dialects add ways to handle duplicate identities during insertion. SQLite’s UPSERT follows PostgreSQL-style ON CONFLICT syntax, with its own details. Our revision rule is an application policy layered on that mechanism; the database does not know which imported status is newer unless we specify it.

Sources for this history

One Status Change Updated the Entire Queue

Botie was asked to close J1. An UPDATE without a WHERE condition closed every job. The SQL was valid, but its target set was wrong.

Writing data requires the same care about row selection as reading it, with a more consequential result. We will add new jobs, accept newer revisions, and delete only the cancelled empty drafts specified by the policy.

Invented maintenance records. SQL programs run in an isolated SQLite database; the workshop illustrates the operation with records rather than parsing SQL.

Step 1 of 1

Keep selection visible before applying a change

Closing the chosen job should not alter the other records.

1
Original queue
J1 open; J2 open; J3 done
2
Selector
id equals J1
3
Patch
status becomes done
4
Merge by ID
J1 done; J2 open; J3 done

Previewing a selector helps inspection; a live transaction still needs protection against concurrent changes.

A write is a selection followed by an effect

The original rows stay present while the selected records receive their field changes.

Does the update preserve unselected jobs?

Select the intended records, replace their fields, then merge them back by identity.

BOTIE

A status update belongs to one selected job.

Choose an example

Current queue

status

Write predicate

Replacement values

status

Updated job records

[
  {
    "id": "J1",
    "status": "done"
  },
  {
    "id": "J2",
    "status": "open"
  }
]

Follow the operations

1. Current queue

[
  {
    "id": "J1",
    "status": "open"
  },
  {
    "id": "J2",
    "status": "open"
  }
]

2. Write predicate
```
{
  "id": "J1"
}
```

3. Match every named selector field

[
  {
    "id": "J1",
    "status": "open"
  }
]

4. Replacement values
```
{
  "status": "done"
}
```

5. Replace selected fields

[
  {
    "id": "J1",
    "status": "done"
  }
]

6. Merge updates by record ID

[
  {
    "id": "J1",
    "status": "done"
  },
  {
    "id": "J2",
    "status": "open"
  }
]

7. Result

[
  {
    "id": "J1",
    "status": "done"
  },
  {
    "id": "J2",
    "status": "open"
  }
]

This is a record transformation. Atomicity and concurrent database writes require the SQL mechanisms in the next lessons.

1. Inspect the set you intend to change

Start with a SELECT using the same intended predicate. Include the primary key and fields that will change. Check whether zero, one or several rows would be expected.

A preview is useful evidence, but another writer can change data after the preview. Keep critical preconditions in the actual UPDATE or DELETE, and use transactions or version checks when the operation depends on previously read state.

A WHERE condition is part of the business operation, not optional punctuation.

2. Insert with an explicit field mapping

INSERT INTO jobs(id,status,minutes,revision) names the destination fields. VALUES adds literal records; INSERT ... SELECT copies records calculated from another table. Constraints apply to the resulting rows in both cases.

An omitted field may receive a default. Explicit NULL has a different meaning from asking for that default. Avoid inserting a full row positionally when schema changes could reorder the fields.

The incoming table is a staging area. Its rows are proposals until the application’s conflict policy accepts them.

3. Update selected rows from their current values

UPDATE jobs SET minutes = minutes + 5 WHERE id = ... expresses an increment relative to the stored value. Fetching a value into the application, adding there, then writing the result can lose a concurrent update.

For status changes, include the expected state when the transition requires one. Check affected-row counts in the application and treat an unexpected zero as a conflict or missing record, rather than announcing success.

The concurrency lesson will make an expected version part of the predicate.

Close one selected job

The predicate leaves other jobs alone.

incoming

id	status	minutes	revision
J1	done	25	2
J3	open	10	2
J4	open	12	1

jobs

id	status	minutes	revision
J1	open	20	1
J2	cancelled	0	1
J3	done	15	3

Database setup for this example

SQL · SQLite

CREATE TABLE jobs(id TEXT PRIMARY KEY NOT NULL,status TEXT NOT NULL,minutes INTEGER NOT NULL,revision INTEGER NOT NULL);
INSERT INTO jobs VALUES('J1','open',20,1),('J2','cancelled',0,1),('J3','done',15,3);
CREATE TABLE incoming(id TEXT,status TEXT,minutes INTEGER,revision INTEGER);
INSERT INTO incoming VALUES('J1','done',25,2),('J3','open',10,2),('J4','open',12,1);

SQL · SQLite

UPDATE jobs SET status='done' WHERE id='J1' AND status='open';
SELECT id,status FROM jobs ORDER BY id;

Query result

id	status
J1	done
J2	cancelled
J3	done

The displayed IDs make the target set visible.

4. Handle repeated identities with an explicit policy

UPSERT handles a uniqueness conflict during insertion. excluded names the proposed row in SQLite’s DO UPDATE clause. Our rule accepts it only if its revision is greater than the stored revision. An older arrival therefore cannot overwrite a newer status.

SQLite INSERT ... SELECT may need a WHERE clause before ON CONFLICT to disambiguate its parser; WHERE true is sufficient when no source filtering is needed. An equal revision with different content is a source-contract error to quarantine, not a reason to pick whichever arrival happened last.

ON CONFLICT handles uniqueness conflicts. It does not excuse violated foreign keys or CHECK constraints.

5. A replacement is not always an update

SQLite INSERT OR REPLACE can delete a conflicting row before inserting a replacement. That can interact with references and other database behaviour differently from updating selected fields. Use a deliberate UPSERT when the intended operation is an update.

Our delete policy removes only cancelled jobs with zero minutes. A completed inspection is historical data and remains. Hard deletion, soft deletion and retention rules need to match the records’ purpose.

Never use a convenient conflict keyword without checking which effect it actually performs.

6. Make repeated delivery predictable

A source can replay the same batch after a failure. A stable primary key and revision comparison make our merge leave the same final rows on a replay. Repeating an unconditional increment would instead add the value again.

Idempotency belongs to the operation’s semantics. It is not a property of every SQL statement. Use operation identities for repeated business effects and keep the transaction boundary aligned with what the application reports as committed.

The last lesson will combine this merge with a durable ingestion checkpoint.

7. Review write results before reporting success

RETURNING can expose changed rows in supported dialects, including the SQLite version used here. An application can also inspect affected-row counts and query the committed result. Choose the method that fits the driver and transaction policy.

Keep errors visible. Do not catch every constraint failure and announce that the batch succeeded. If a batch deliberately quarantines rejected rows, record which ones failed and why so the import can be reconciled.

The challenge’s final SELECT is a visible result. Its tests also replay data and add records the original example did not contain.