DML - MySQL (DELETE)

Delete a record in a table

Synatx

DELETE FROM <table_name> WHERE <condition>;

Example

+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
|          3 | Ben          |   15 | M      |
|          4 | Jane         |   16 | F      |
+------------+--------------+------+--------+

• Delete Ben from student table.

Usage

mysql> DELETE FROM student
    -> WHERE student_id = 3
    -> AND student_name = "Ben";

Output:

Results:

Query OK, 1 row affected (0.01 sec)

Validation:

mysql> SELECT * FROM student;
+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
|          4 | Jane         |   16 | F      |
+------------+--------------+------+--------+
3 rows in set (0.00 sec)

Conclusion

WARNING

• DELETE syntax will remove entire record (field and it's data), So if we only want to delete a record of a field, we have to use UPDATE and SET the value to NULL.

• Delete without any condition will remove all of the records in a table.