DELETE Statements

Explicit All-Rows DELETE

All rows are removed only by an explicit all-rows predicate.

highlighted = computed this step

Explicit all-rows DELETE

This model deletes every row only when the predicate is explicitly all rows.

all rows explicit\text{all rows explicit}

Remove all rows

The WHERE kind is all, so DELETE removes 4 rows and keeps 0.

kept=0\text{kept}=0

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

Explicit all-rows DELETE: base rowsidownerstatuspoints1Adaopen102Benopen203Cydone154DeeNULL5 WHERE chooses rowssourcevaluecompareTotruthremoved0ALL_ROWSALL_ROWSTRUEyes1ALL_ROWSALL_ROWSTRUEyes2ALL_ROWSALL_ROWSTRUEyes3ALL_ROWSALL_ROWSTRUEyes Rows removedsourcerowreason0(1, Ada, open, 10)WHERE_TRUE1(2, Ben, open, 20)WHERE_TRUE2(3, Cy, done, 15)WHERE_TRUE3(4, Dee, NULL, 5)WHERE_TRUE Rows after DELETEidownerstatuspointsnonenonenonenone DELETE factsfactvaluebaseRowCount4removedRowCount4keptRowCount0finalRowCount0whereUnknownCount0deleteStatusremoved_rowswhereKindall

Read empty output

The after table says none because every source row was TRUE.

empty table\text{empty table}

Summary

The book does not model accidental missing-WHERE deletes.

explicit predicate\text{explicit predicate}