DELETE Statements

Table before DELETE

DELETE starts from visible rows.

highlighted = computed this step

Table before DELETE

Start with the exact rows before a DELETE removes anything.

table first\text{table first}

Show base rows

The table has 4 rows and 4 columns.

rows=4\text{rows}=4

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.

Table before DELETE: base rowsidownerstatuspoints1Adaopen102Benopen203Cydone154DeeNULL5 WHERE chooses rowssourcevaluecompareTotruthremoved012FALSEno122TRUEyes232FALSEno342FALSEno Rows removedsourcerowreason1(2, Ben, open, 20)WHERE_TRUE Rows after DELETEidownerstatuspoints1Adaopen103Cydone154DeeNULL5 DELETE factsfactvaluebaseRowCount4removedRowCount1keptRowCount3finalRowCount3whereUnknownCount0deleteStatusremoved_rowswhereKindeq

Keep row order

Rows stay in source order until the removed rows disappear.

source order\text{source order}

Summary

Every later DELETE fact is recomputed from these pinned rows.

pinned rows\text{pinned rows}