Concepts / DELETE: Removing Records from a Table

DELETE: Removing Records from a Table

UPDATE modifies existing records in a table using the SET keyword to specify columns and new values.

  • Programming
Interactive lab

Try it: INSERT, UPDATE and DELETE

How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.

How it works

  1. The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
  2. INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
  3. UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
  4. UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
  5. cur.rowcount is the number of rows inserted, changed or removed.
  6. The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.

Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.

Educational simulation

Loading the simulation…

Why Existing Records Change

Database records often need correction or revision. A customer may change an email address, a product price may increase, or a song's play count may go up. UPDATE is the SQL statement used to modify values in columns of records that already exist in a table.

Definition

UPDATE modifies existing records in a table. The SET clause identifies the columns and replacement values, while an optional WHERE clause determines which rows are changed.

How WHERE Selects Rows

The WHERE clause filters the table before the update is applied. The database checks each row against the condition. Rows that match are changed; rows that do not match remain untouched. If several rows satisfy the condition, one UPDATE statement changes all of them. If no row satisfies it, the statement changes nothing.

Track tableexisting rowstitle = 'My Way'check each rowMatching rowsplays becomes 16Other rowsremain untouchedevaluatematchesdoes not match
How does the database determine which rows will be changed by an UPDATE statement?
sql

This statement targets rows in Track whose title is exactly My Way and changes their plays value to 16. If multiple rows have that title, all matching rows are updated. If no row has that title, no rows change.

Mapping Columns with SET

The SET clause describes the replacement values. Each assignment names a column, uses an equals sign, and gives the new value. Multiple assignments are separated by commas, so one UPDATE can change several columns in the same row or rows.

UPDATEchoose tableCustomertableSETbegin assignmentsemailnew email value,separate assignmentsphonenew phone valueWHEREfilter rowsnamesusesassignsthenassignsthen filters
How does each column named in SET map to its new value?
sql

Here, customer_id = 5 identifies the target customer. Both email and phone are changed in the same operation. The assignments in SET are separated by commas, and their order does not matter because SQL processes them together.

Selected Rows and Every Row

A WHERE clause limits the update to rows that satisfy its condition. Omitting WHERE removes that filter. In that case, every row in the table is modified. This is the critical difference between a targeted update and an all-rows update.

pending ordersbeforeprocessed ordersWHERE status = 'pending'other ordersbeforeother ordersuntouchedall rowsbeforenew values in allrowsno WHERE clausetargeted updatenot matchedall rows updated
What changes in the table when an UPDATE includes a WHERE clause versus when it omits one?
sql

This statement changes every pending order to processed. If 100 orders are pending, all 100 are updated by the one statement. The statement is efficient because it handles matching rows together, but the condition must be checked carefully.

Checking Before Changing

Worked example

Testing the Target Rows

You want to update every pending order to processed.

  1. 1
    Write a SELECT with the intended filter

    Use the same WHERE condition that you plan to use with UPDATE so you can inspect the rows that would be affected.

  2. 2
    Inspect the returned rows

    Confirm that the result contains exactly the records you intend to change. Unexpected rows indicate that the condition needs revision.

  3. 3
    Run the UPDATE only after verification

    Use the verified condition in the UPDATE statement to change the matching rows.

Result

The SELECT acts as a check on the WHERE clause before the data is modified.

sql

Run the SELECT first and inspect its rows. If it returns exactly the records you expect, reuse the same WHERE clause in the UPDATE. If it returns unexpected rows, revise the condition and test again before making any change.

Mistakes to Avoid

  • Common mistake

    Omitting the WHERE clause when only selected rows should change

    What it looks like
    UPDATE Customer SET email = 'new@example.com';
    Why it fails

    Without WHERE, every row in Customer is modified.

    Do this instead

    Add a condition that identifies the intended row or rows, then verify that condition with SELECT.

  • Common mistake

    Using a condition that matches more rows than intended

    What it looks like

    A title condition may match multiple rows if several records have the same title.

    Why it fails

    Every row matching the condition is updated, not just the row you had in mind.

    Do this instead

    Choose and verify a WHERE condition that targets the intended records.

  • Common mistake

    Skipping the verification step

    What it looks like

    Running UPDATE immediately without first checking the same WHERE condition with SELECT.

    Why it fails

    You lose the opportunity to inspect the rows before changing them.

    Do this instead

    Run SELECT with the exact same WHERE clause first.

Practice the Decision

EASY

Write an UPDATE statement that changes the phone column for the customer whose customer_id is 5. Then describe how you would verify the target row before running the UPDATE.

Hints
  • Name the Customer table after UPDATE.
  • Use SET to assign a new value to phone.
  • Use WHERE customer_id = 5 to identify the intended customer.
  • Test the same WHERE condition with SELECT first.

What do you think happens?

Suppose a table contains 100 pending orders. How many rows will this statement update: UPDATE Order SET status = 'processed' WHERE status = 'pending';

  • One row

  • Only the first matching row

  • All 100 pending rows

  • No rows

Reveal answer
Answer:

All 100 pending rows

A single UPDATE changes every row that matches its WHERE condition.

Key Takeaways

  1. 1
    UPDATE modifies values in records that already exist.
  2. 2
    SET names the columns and replacement values.
  3. 3
    WHERE determines which rows are changed; matching multiple rows updates all of them.
  4. 4
    Without WHERE, every row in the table is modified.
  5. 5
    Verify the exact WHERE clause with SELECT before running UPDATE.

At a glance

  • UPDATE changes existing records rather than adding new rows.
  • SET specifies one or more column assignments.
  • WHERE filters the rows that receive the update.
  • A missing WHERE clause causes every row in the table to be modified.
  • Use SELECT with the same WHERE clause to verify the target rows before updating.

More concepts starting with D