Deleting records from a table

The DELETE statement is used to delete records in a table. If works very similar to the UPDATE statement in which you will need to use the conditional keyword WHERE to isolate which records you wish to delete. The basic syntax for the statement is as follows:

DELETE FROM table_name
WHERE field_name = value;

Let’s say that we need to remove Jane Smith from our student table. She has a student_id value of 12544. We could write the following statement:

DELETE FROM student
WHERE student_id=12544;

This would delete the entire record for Jane Smith. Similar to using the UPDATE command it is common to use the primary key of the table to isolate a single record. If we would have used the following command:

DELETE FROM student
WHERE first_name="Jane" AND last_name="Smith";

Then all the students in the table with the first name of Jane and the last name of Smith would have been deleted. There may have been more than one Jane Smith in the database.

Also consider the following code:

DELETE FROM student
WHERE first_name="Jane" OR last_name="Smith";

Oh boy, that would have deleted all records of students with the first name Jane. In addition to that, it would have also deleted all records of students with the last name Smith. It is a good idea to double check your DELETE statements as a mistake is often unrecoverable.

Lesson Activities

  1. We are going to delete Bob Smith from the faculty table. First we should look to see what his faculty_id number is. Use the following code:
    Click to see code
    SELECT *
    FROM faculty
    WHERE last_name="Smith";
  2. Bob Smith has a faculty_id of 5 in my faculty table. To delete the record I will use the following code:
    Click to see code
    DELETE FROM faculty
    WHERE faculty_id=5;
  3. To see if Bob Smith is still in the table, use the following command:
    Click to see code
    SELECT * FROM faculty;

Questions