Remove a row

You can use the DELETE command to remove a row from a table. The syntax:

DELETE FROM table_name WHERE column_name operator value

Here is an example. We have our old table testtb:

mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 | John | Jones | 1985 |
 +------+-------------+------+
 4 rows in set (0.01 sec)

Let’s say that we want to delete the last row. We can do it by specifying the surname Jones with the WHERE clause:

mysql> DELETE FROM testtb WHERE surname='Jones';
 Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 +------+-------------+------+
 3 rows in set (0.00 sec)

 

Make sure to include the WHERE clause. If you omit it, all records in the table will be deleted!
Geek University 2021