Updating records in a table

The UPDATE statement is used to update existing records in a table. It is used with the condition clause WHERE. This allows us to isolate the specific record or records we wish to change. Often times if we are wanting to isolate one record to change, the primary key is used in the condition. We can however update multiple records with a specific value if we choose a field that has repeating values.

Below is an example of the syntax for the UPDATE command with two fields being changed.

UPDATE table_name
SET field = value
WHERE field_name = existing_value;

If we wanted to choose the last_name of a student (Smith) whose student_id is 12664 to Jones we would use the following UPDATE command:

UPDATE student
SET last_name="Jones"
WHERE student_id=12664;

This would update just the record in the database that has a student_id of 12664.

Lets say for example that some of the records in your table contain the word Virginia for the state rather than VA. Any record that has Virginia as the value of state can be changed to VA by using the following command:

UPDATE student
SET state="VA"
WHERE state="Virginia";

Lesson Activities

  1. We will need to update the phone number of William Hartnell in the faculty table. First lets look to see what the faculty_id number is for him. Use the command:
    Click to see code
    SELECT * FROM faculty;
  2. In my table, William Hartnell has a faculty_id number of 1. Now to update his phone number I will use the following code:
    Click to see code
    UPDATE faculty
    SET phone="(555) 444-5555"
    WHERE faculty_id=1;
  3. We will now update the phone number of Patrick Troughton in the faculty table. In my table, he has a faculty_id number of 2. Now to update his phone number and remove the value I will use the following code:
    Click to see code
    UPDATE faculty
    SET phone=NULL
    WHERE faculty_id=2;
  4. View the results of your table using:
    Click to see code
    SELECT * FROM faculty;
  5. A common mistake that can be made is forgetting the WHERE clause. Let’s see what happens when we omit the WHERE clause.
    Click to see code
    UPDATE faculty
    SET phone="(555)-444-3333";
  6. Now run the SELECT command again to view the results. Because the phone number is not crucial for the course we will leave the values.
    Click to see code
    SELECT * FROM faculty;
  7. Update the record for student Clara Oswald to have assigned faculty_id number 1
    Click to see code
    UPDATE student
    SET faculty_id = 1
    WHERE first_name = "Clara";
  8. Update the record for student Amy Pond to have assigned faculty_id number 2
    Click to see code
    UPDATE student
    SET faculty_id = 2
    WHERE last_name = "Pond";
  9. View the results of the student table.
    Click to see code
    SELECT * FROM student;

Questions