Concepts / SELECT: Retrieving Data from Tables

SELECT: Retrieving Data from Tables

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…

Changing Data Already Stored

Data in a database can change after a row has been created. A customer may update an email address, a product price may change, or a song's play count may increase. UPDATE is the SQL statement used to modify values in columns of records that already exist in a table. INSERT adds new rows; UPDATE changes values in rows that are already present.

Key point

The central question when using UPDATE is not only what value should change, but also which rows should receive that change.

My Wayplays: 8My Wayplays: 16UPDATE
What changes when an UPDATE targets the existing Track record titled My Way?

Reading an UPDATE Statement

An UPDATE statement has three important parts. UPDATE identifies the table whose existing records may change. SET lists the column assignments, pairing each column with its new value. WHERE filters the rows that are eligible for the change. The WHERE clause is optional in the statement's structure, but omitting it means that every row in the table is modified.

sql

This statement targets the Track table, assigns the value 16 to the plays column, and limits the update to rows whose title equals My Way. If several rows have that title, all matching rows are updated. If no row matches, the statement runs without changing any rows.

UPDATEchoose tableTracktarget tableSETassign valuesplays = 16column and new valueWHEREfilter rowstitle = My Waymatching conditionnamesthencontainsthencontains
How does control move from choosing a table to assigning values only in matching rows?

Selecting Rows with WHERE

WHERE determines which existing records are changed and which remain untouched. In the Track example, title = 'My Way' is the condition. Rows with another title do not receive the new plays value. A condition may match one row, many rows, or no rows, so its scope must be considered before the statement runs.

Code walkthrough
UPDATE Track
SET plays = 16
WHERE title = 'My Way';
  1. 1
    The database considers rows in Track.
  2. 2
    It checks which rows satisfy the WHERE condition.
  3. 3
    For each matching row, it changes plays to 16.
  4. 4
    Rows that do not satisfy the condition remain unchanged.
  • UPDATE Track selects the table containing the existing records.
  • SET plays = 16 assigns the new value to the plays column.
  • WHERE title = 'My Way' limits the change to matching rows.
My Wayplays: 8My Wayplays: 16Other songplays: 4Other songplays: 4matches WHEREdoes not match
Which rows will change when the UPDATE matches the title My Way, and which rows will remain unchanged?

One Row or Every Row

Statement formRows affectedSafety implication
UPDATE with WHERERows matching the conditionOnly the selected rows are modified
UPDATE without WHEREEvery row in the tableAll rows receive the change

The presence or absence of WHERE controls the scope of an UPDATE.

sql

This statement changes every order whose status is pending to processed. If 100 orders match the condition, all 100 are updated by this one statement. That is useful when the same change is intended for a group, but it also makes an overly broad condition consequential.

sql

The second statement has no WHERE clause. Therefore, it modifies the status value in every row of Order, not only rows that were previously pending. The difference between these two statements is a single clause, but the number of affected records can be dramatically different.

UPDATE with WHEREmatching rowsSelected rowschangedUPDATE withoutWHEREall rowsEvery rowchangedchangeschanges
What happens to the table when WHERE is present compared with when WHERE is omitted?

Changing Several Columns

SET can contain more than one column assignment. Separate the assignments with commas. This allows one UPDATE statement to change several values in the same row or rows. For example, a customer may update both an email address and a phone number in one operation.

sql

The WHERE condition identifies customer 5. Both assignments in SET apply to that customer's record. The order of the assignments does not matter; SQL processes them together.

emailnew@example.comCustomer 5receives both assignmentsphone555-0100assigned toassigned to
How are the named columns connected to the new values they receive?

Verify Before You Modify

sql

The SELECT is used as a check: it shows which rows satisfy the condition. The UPDATE then uses that same condition to modify those rows. This is especially important because a condition may match multiple records, and an UPDATE can change all of them at once.

Common UPDATE Mistakes

  • Common mistake

    Leaving out the WHERE clause when only selected rows should change

    What it looks like
    UPDATE Track SET plays = 16;
    Why it fails

    Without WHERE, every row in Track is modified.

    Do this instead

    Add and verify a condition that identifies the intended rows.

  • Common mistake

    Using a condition that matches more rows than intended

    What it looks like
    WHERE title = 'My Way'
    Why it fails

    If several rows have that title, all of them are updated.

    Do this instead

    Use a condition that identifies the intended records, and test it with SELECT first.

  • Common mistake

    Changing only one column when several related values need updating

    What it looks like

    Updating the email but not the phone number for a customer who changed both

    Why it fails

    The record remains partly outdated.

    Do this instead

    Place both column assignments in SET, separated by commas.

  • Common mistake

    Assuming that no matching row is an error

    What it looks like

    A WHERE condition matches no records.

    Why it fails

    The statement can run without error while changing nothing.

    Do this instead

    Check the condition and confirm that the intended record exists.

Practice the Target

EASY

Write an UPDATE statement that changes the email and phone columns for the customer whose customer_id is 5. Then describe what could happen if you removed the WHERE clause.

Hints
  • Begin with UPDATE and the Customer table.
  • Put both column assignments in SET and separate them with a comma.
  • Use customer_id = 5 as the WHERE condition.
  • Without WHERE, every row in Customer would be modified.
sql

Before executing an update like this, use a SELECT with WHERE customer_id = 5 to verify that the returned record is the customer you intend to modify.

Essential Takeaways

  1. 1
    UPDATE modifies values in existing records; it does not add new rows.
  2. 2
    SET names the columns and supplies their new values.
  3. 3
    A WHERE clause filters the rows that receive the update.
  4. 4
    Without WHERE, every row in the table is modified.
  5. 5
    Use SELECT with the same WHERE clause before UPDATE to verify the intended target rows.

At a glance

  • UPDATE changes values in records that already exist in a table.
  • SET specifies one or more column and value assignments.
  • WHERE determines which rows are changed; without it, all rows are changed.
  • One UPDATE can affect many matching rows or no rows at all.
  • Verify the target rows with SELECT before running UPDATE.

Related Topics

Related concepts

More concepts starting with S