CRUD Fundamentals: Create, Read, Update, Delete

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.

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.

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

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.

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

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.

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.

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.

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

• Running UPDATE without checking the WHERE clause

  Every row that satisfies the condition is modified, and a missing WHERE clause modifies every row.

  Fix: Run SELECT with the exact same WHERE clause first and inspect the returned rows.

• Assuming a WHERE condition matches only one row

  A single UPDATE changes all rows that match the condition.

  Fix: Check how many and which rows the condition selects before updating.

• Using SET for only one column when several related values must change

  The intended record is only partially updated.

  Fix: List each required column assignment in SET, separated by commas.

• Expecting an unmatched condition to report an error

  The statement can run without error while changing nothing.

  Fix: Use a SELECT with the condition to confirm that the intended row exists.

Practice the Decision

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.