SELECT: Retrieving Data from Tables

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.

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.

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

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.

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.

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

One Row or Every Row

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

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.

UPDATE Order
SET status = 'processed';

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.

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.

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

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.

Verify Before You Modify

SELECT *
FROM Track
WHERE title = 'My Way';

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

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

• Leaving out the WHERE clause when only selected rows should change

  Without WHERE, every row in Track is modified.

  Fix: Add and verify a condition that identifies the intended rows.

• Using a condition that matches more rows than intended

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

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

• Changing only one column when several related values need updating

  The record remains partly outdated.

  Fix: Place both column assignments in SET, separated by commas.

• Assuming that no matching row is an error

  The statement can run without error while changing nothing.

  Fix: Check the condition and confirm that the intended record exists.

Practice the Target

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

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