UPDATE Statements

Multiple SET Columns

A matched row can receive several assignments.

highlighted = computed this step

Multiple SET columns

A matched row can receive more than one assignment.

several SET columns\text{several SET columns}

Set status and points

The statement has 2 SET columns and changes 4 cells.

SET columns=2\text{SET columns}=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 SET columns: base rowsidownerstatuspointsnote1Adaopen10seed2Benopen20call3Cydone15NULL4DeeNULL5draft WHERE chooses rowssourcevaluecompareTotruthkeptForUpdate0openopenTRUEyes1openopenTRUEyes2doneopenFALSEno3NULLopenUNKNOWNno SET assignmentscolumnnewValuestatusdonepoints0 Row-by-row updatesourcematchedassignmentsafter0yes({column:status, old:open, new:done}, {column:points, old:10, new:0})(1, Ada, done, 0, seed)1yes({column:status, old:open, new:done}, {column:points, old:20, new:0})(2, Ben, done, 0, call)2no()(3, Cy, done, 15, NULL)3no()(4, Dee, NULL, 5, draft) Rows after UPDATEidownerstatuspointsnote1Adadone0seed2Bendone0call3Cydone15NULL4DeeNULL5draft UPDATE factsfactvaluebaseRowCount4finalRowCount4matchedRowCount2setColumnCount2changedCellCount4nullAssignmentCount0updateStatusappliedwhereUnknownCount1

Read assignments

Each matched row shows both assignments in the row-by-row update table.

two assignments\text{two assignments}

Summary

Multiple SET columns multiply across matched rows.

matches times assignments\text{matches times assignments}