UPDATE Statements

One-Column SET

One SET column changes one cell per matched row.

highlighted = computed this step

One-column SET

A one-column SET changes one named cell on each matched row.

one SET column\text{one SET column}

Change points

The statement has 1 SET column and writes 1 cell.

changed cells=1\text{changed cells}=1

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.

One-column SET: 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

Read after row

The matched row keeps its other cells and receives the new points value.

one cell changed\text{one cell changed}

Summary

The output table is the base table plus the visible assignment.

recomputed output\text{recomputed output}