UPDATE Statements

Multiple Rows Affected

The same assignment applies to every matched row.

highlighted = computed this step

Multiple rows affected

A WHERE predicate can choose more than one row.

many TRUE rows\text{many TRUE rows}

Close open tasks

WHERE matches 2 rows and changes 2 cells.

matched=2\text{matched}=2

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.

Multiple rows affected: base rowsidownerstatuspointsnote1Adaopen10seed2Benopen20call3Cydone15NULL4DeeNULL5draft WHERE chooses rowssourcevaluecompareTotruthkeptForUpdate0openopenTRUEyes1openopenTRUEyes2doneopenFALSEno3NULLopenUNKNOWNno SET assignmentscolumnnewValuestatusdone Row-by-row updatesourcematchedassignmentsafter0yes({column:status, old:open, new:done})(1, Ada, done, 10, seed)1yes({column:status, old:open, new:done})(2, Ben, done, 20, call)2no()(3, Cy, done, 15, NULL)3no()(4, Dee, NULL, 5, draft) Rows after UPDATEidownerstatuspointsnote1Adadone10seed2Bendone20call3Cydone15NULL4DeeNULL5draft UPDATE factsfactvaluebaseRowCount4finalRowCount4matchedRowCount2setColumnCount1changedCellCount2nullAssignmentCount0updateStatusappliedwhereUnknownCount1

Read each matched row

Both open rows receive the same new status value.

same assignment per match\text{same assignment per match}

Summary

UPDATE applies row by row to every TRUE row.

row by row\text{row by row}