UPDATE Statements

WHERE Chooses Rows

WHERE TRUE rows are chosen for update.

highlighted = computed this step

WHERE chooses rows

The WHERE predicate marks which rows are TRUE for update.

WHERE then SET\text{WHERE then SET}

Read WHERE results

WHERE matches 1 row and produces 0 UNKNOWN result here.

matched=1\text{matched}=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.

WHERE chooses rows: 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

Only TRUE updates

Rows marked FALSE or UNKNOWN are not updated.

TRUE rows only\text{TRUE rows only}

Summary

The SET list is applied only after WHERE chooses rows.

chosen rows\text{chosen rows}