Deleting Rows

A student leaves the school. delete takes whole rows out of a table — and only whole rows. It is the shortest command in the chapter and the one to be most careful with, for exactly the same reason as update: the where is optional.

1The syntax

delete from <table name>
 where <condition>;

Notice there is no list of columns anywhere. There is nowhere to put one, because a row is the smallest thing delete can remove.

2Deleting one row

MySQL command line client

mysql> delete from students where id = 3033;

Query OK, 1 row affected (0.03 sec)

mysql> select id, name from students;

+------+-------+

| id | name |

+------+-------+

| 1099 | Veena |

| 2035 | Ravi |

| 4001 | Asha |

+------+-------+

3 rows in set (0.00 sec)

Tom's row is gone: his id, his name, his marks, his date of birth, all of it. Four rows became three. The table's cardinality dropped by one and its degree did not move, because columns are structure and delete does not touch structure.

delete cannot empty a single cell

“Delete Tom's phone number” is not a job for delete — that would leave the rest of Tom's row in place, which means the row is being changed, not removed. The command for it is update students set phone = NULL where id = 3033;. This is a favourite exam distinction.

3Deleting every row

Leave out the where and there is no condition to limit it, so every row qualifies:

MySQL command line client

mysql> delete from students;

Query OK, 3 rows affected (0.04 sec)

mysql> select * from students;

Empty set (0.00 sec)

mysql> select count(*) from students;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

The table still exists — that is what Empty set and a count of 0 are telling you. It has its six columns, its data types and its primary key, all intact and ready. It simply has nothing in it.

4delete, drop and truncate

Three commands that remove things

Command	Removes	Table still exists?	Group
delete from t where …;	The rows that match	Yes	DML
delete from t;	Every row	Yes, empty	DML
drop table t;	Every row AND the table itself	No	DDL

Where the marks are

Questions in this area almost always turn on one word. “Remove all records” means delete. “Remove the table” or “remove the structure” means drop. Read the question for those words before writing anything.

5When the where matches nothing

MySQL command line client

mysql> delete from students where id=99999;

Query OK, 0 rows affected (0.00 sec)

No error. There was simply no row with that id, so nothing was removed. As with update, 0 rows affected when you expected one means the condition is wrong.

Check with a select first

The same habit as the last lesson, and it matters more here because deletion cannot be undone. Run select * from students where …; with the exact condition you are about to use. The rows it shows you are the rows that will disappear.