CRUD Fundamentals: Create, Read, Update, Delete

An UPDATE statement has three central parts: the UPDATE keyword and table name, a SET clause containing the new column values, and an optional WHERE clause that determines which rows receive the change.

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

The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
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.
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.
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.
cur.rowcount is the number of rows inserted, changed or removed.
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 Existing Data

Database data changes constantly. A customer may update an email address, a product price may change, or a song's play count may increase. UPDATE is the SQL operation used to modify values in columns of records that already exist in a table. Unlike INSERT, which adds new rows, UPDATE changes existing records.

Key point

Following One Update

Suppose a Track table contains a song titled My Way with 8 recorded plays. To change that value to 16, the statement identifies the table, names the column to change, supplies the new value, and uses a condition to identify the matching row. The WHERE clause is what keeps rows with other titles untouched.

sql

UPDATE Track
SET plays = 16
WHERE title = 'My Way';

UPDATE Track
SET plays = 16
WHERE title = 'My Way';

Which record changes when the WHERE clause matches the title My Way?

Output

The matching row has plays changed from 8 to 16. Rows whose title does not match My Way remain untouched.

Reading the UPDATE Structure

The UPDATE keyword identifies the operation, followed by the table whose existing records may change. SET lists the column assignments: each named column receives a new value. WHERE filters the rows that are allowed to receive those assignments. If multiple rows satisfy the WHERE condition, the same SET changes are applied to all of those matching rows.

sql

UPDATE table_name
SET column_a = new_value,
    column_b = another_value
WHERE condition;

UPDATE table_name
SET column_a = new_value,
    column_b = another_value
WHERE condition;

How does each part of the statement contribute to changing selected records?

A SET clause can contain one assignment or several. Separate multiple column assignments with commas. For example, changing both a customer's email and phone number can be handled in one UPDATE statement. The order of the assignments does not matter; SQL processes them together.

sql

UPDATE Customer
SET email = 'new_email@example.com',
    phone = '555-0100'
WHERE customer_id = 5;

UPDATE Customer
SET email = 'new_email@example.com',
    phone = '555-0100'
WHERE customer_id = 5;

Selecting Rows Safely

WHERE is the most important safety feature of UPDATE because it determines which rows change and which remain untouched. A condition may match one row, many rows, or no rows. If no row matches, the statement runs without error but changes nothing.

sql

SELECT *
FROM Order
WHERE status = 'pending';

UPDATE Order
SET status = 'processed'
WHERE status = 'pending';

SELECT *
FROM Order
WHERE status = 'pending';

UPDATE Order
SET status = 'processed'
WHERE status = 'pending';

The same condition can match many records. In the order example, every row whose status is pending receives the new status processed. If 100 orders match, all 100 are updated by that one statement. This is efficient, but it makes checking the WHERE condition essential.

With WHERE and Without It

Leaving out WHERE changes the scope completely. With WHERE, only rows satisfying the condition are modified. Without WHERE, every row in the table is modified. The SET clause still identifies the column and new value, but no row filter remains to protect the other records.

What happens to a table when an UPDATE includes a WHERE clause compared with when the clause is omitted?

sql

UPDATE Track
SET plays = 16
WHERE title = 'My Way';

UPDATE Track
SET plays = 16;

UPDATE Track
SET plays = 16
WHERE title = 'My Way';

UPDATE Track
SET plays = 16;

The first statement changes only rows whose title is My Way. The second statement changes the plays value for every row in Track. Treat an UPDATE without WHERE as an intentional whole-table operation only when changing every row is truly what you want.

Mistakes to Avoid

Common mistake
Running UPDATE without checking the WHERE clause
What it looks like
An incorrect condition can match unintended records.
Why it fails
Every row that satisfies the condition is modified, and a missing WHERE clause modifies every row.
Do this instead
Run SELECT with the exact same WHERE clause first and inspect the returned rows.
Common mistake
Assuming a WHERE condition matches only one row
What it looks like
WHERE title = 'My Way' can match multiple rows if that title appears more than once.
Why it fails
A single UPDATE changes all rows that match the condition.
Do this instead
Check how many and which rows the condition selects before updating.
Common mistake
Using SET for only one column when several related values must change
What it looks like
Updating a customer's email but forgetting the phone value that should change in the same operation.
Why it fails
The intended record is only partially updated.
Do this instead
List each required column assignment in SET, separated by commas.
Common mistake
Expecting an unmatched condition to report an error
What it looks like
WHERE customer_id = 5 when no row has that customer ID.
Why it fails
The statement can run without error while changing nothing.
Do this instead
Use a SELECT with the condition to confirm that the intended row exists.

Practice the Decision

Worked example

Updating a Customer Record

A customer with customer_id = 5 has changed both their email and phone number. Which parts of the UPDATE statement identify the changed values and the target record?

1
Choose the table
Use the Customer table because the existing customer record is being modified.
2
List new values
Use SET to assign the new email and phone values. Separate the two assignments with a comma.
3
Target the record
Use WHERE customer_id = 5 so the change applies to the customer identified by 5 rather than to every customer.
4
Verify first
Run SELECT with the same WHERE condition and confirm that it returns the intended customer before running UPDATE.

Result

SET specifies which columns change and their new values; WHERE specifies which existing record or records receive those changes.

EASY

A Track table contains several songs. You need to change the plays value to 16 only for rows whose title is My Way. Identify the table, the SET assignment, and the WHERE condition. Then explain what could happen if the WHERE clause were removed.

Hints

UPDATE names the table containing the existing records.
SET contains the column and new value.
WHERE title = 'My Way' selects the matching rows.
Without WHERE, every row in the table is modified.

Your attempt (not saved)

Key Takeaways

1
UPDATE modifies values in records that already exist in a table.
2
SET names the columns to change and supplies their new values.
3
WHERE filters the rows that receive the update.
4
One UPDATE can change multiple columns and every row matching its WHERE condition.
5
Always verify the target rows with SELECT before running UPDATE, especially because omitting WHERE modifies every row.

At a glance

UPDATE changes existing records rather than adding new rows.
SET specifies one or more column assignments.
WHERE controls which rows are changed.
A missing WHERE clause modifies every row in the table.
Testing the same condition with SELECT is a key safety practice.

More concepts starting with C