Data Deletion

Records do not stay in a table forever. When an entity leaves the picture — a student leaves the school, an employee resigns — its record has to go. The DELETE statement is used to delete one or more record(s) from a table.

Its syntax:

DELETE FROM table_name
WHERE condition;

Notice what is absent from this statement: a column list. DELETE removes entire rows, never individual column values. The WHERE clause decides which rows go, and everything in those rows goes together.

Suppose the student with roll number 2 has left the school. That record is removed from the STUDENT table with:

DELETE FROM STUDENT WHERE RollNumber = 2;

MySQL confirms:

Query OK, 1 row affected (0.06 sec)

Running SELECT * FROM STUDENT; afterwards shows the table without that row:

+------------+--------------+--------------+--------------+
| RollNumber | SName        | SDateofBirth | GUID         |
+------------+--------------+--------------+--------------+
| 1          | Atharv Ahuja | 2003-05-15   | 444444444444 |
| 3          | Taleem Shah  | 2002-02-28   | 101010101010 |
| 4          | John Dsouza  | 2003-08-18   | 333333333333 |
| 5          | Ali Shah     | 2003-07-05   | 101010101010 |
| 6          | Manika P.    | 2002-03-10   | 466444444666 |
+------------+--------------+--------------+--------------+
5 rows in set (0.00 sec)

The remaining five records are intact. Only the row matching the condition has disappeared — deletion removes a row, it does not renumber or reshuffle the rest, which is why the roll numbers now jump from 1 to 3.