UPDATE Statements

Table before UPDATE

UPDATE starts from visible rows.

highlighted = computed this step

Table before UPDATE

Start with the exact rows before an UPDATE changes anything.

table first\text{table first}

Show base rows

The table has 4 rows and 5 columns.

rows=4\text{rows}=4

UPDATE examples are tiny finite row modifications; SQL dialect completeness, joins in UPDATE, subqueries, triggers, constraints, transactions, locks, indexes, optimizer behavior, performance, and product behavior are out of scope.

Table before UPDATE: base rowsidownerstatuspointsnote1Adaopen10seed2Benopen20call3Cydone15NULL4DeeNULL5draft WHERE chooses rowssourcevaluecompareTotruthkeptForUpdate012FALSEno122TRUEyes232FALSEno342FALSEno SET assignmentscolumnnewValuepoints25 Row-by-row updatesourcematchedassignmentsafter0no()(1, Ada, open, 10, seed)1yes({column:points, old:20, new:25})(2, Ben, open, 25, call)2no()(3, Cy, done, 15, NULL)3no()(4, Dee, NULL, 5, draft) Rows after UPDATEidownerstatuspointsnote1Adaopen10seed2Benopen25call3Cydone15NULL4DeeNULL5draft UPDATE factsfactvaluebaseRowCount4finalRowCount4matchedRowCount1setColumnCount1changedCellCount1nullAssignmentCount0updateStatusappliedwhereUnknownCount0

Keep row order

Rows stay in source order; UPDATE changes cells, not the display order.

source order\text{source order}

Summary

Every later fact is recomputed from these pinned rows.

pinned rows\text{pinned rows}