This lecture covers the UPDATE command to modify existing data.
So far, we have learned how to create data (`INSERT`) and read it (`SELECT`). These are two of the four fundamental operations in database management, often called CRUD (Create, Read, Update, Delete).
Today, we learn the “U” in CRUD: Update. The UPDATE command is used to modify data that is already in a table.
The `UPDATE` Statement
Let’s say a contact in our address book, Jane Doe, moves to a new address. We don’t want to delete her and re-create the record; we just want to change the value in the `Address` column for her specific row.
The syntax for the UPDATE command is:
UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE condition;
EXTREMELY IMPORTANT: The WHERE clause in an UPDATE statement is critical. It specifies *which row(s)* to update. If you forget the WHERE clause, you will update every single row in the table! Always double-check your WHERE clause before running an UPDATE.
Updating a Record
Let’s update Jane Doe’s address to “999 New St, Newcity”. We will use her `ContactID` to uniquely identify her record. Let’s assume her ID is 1.
UPDATE Contacts
SET Address = '999 New St, Newcity'
WHERE ContactID = 1;Let’s break it down:
UPDATE Contacts: We specify the table to be updated.SET Address = '999 New St, Newcity': We specify the column to change (`Address`) and provide its new value.WHERE ContactID = 1: This is our safety net. It ensures that only the row where the `ContactID` is 1 will be affected.
Updating Multiple Columns
You can update multiple columns at the same time. Let’s say John Smith gets a new phone number and a new email address.
UPDATE Contacts
SET Phone = '555-1111', Email = 'john.s@newemail.com'
WHERE FirstName = 'John' AND LastName = 'Smith';Here, we used a different condition in our `WHERE` clause to find the correct record. After running this, you can verify the changes by running SELECT * FROM Contacts;.
In the Next Lecture…
We will learn the final, and most dangerous, of the CRUD operations: the “D” for Delete. You will learn how to permanently remove rows from your database using the DELETE command.
