Delete statement

16

To delete one or multiple records from a table you use delete statement.

delete from `user` where `id` = 1;
Query OK, 1 row affected (0.05 sec)

Let's look into the table:

select * from `user`;
+----+----------+-----------------------+--------------+---------------------+
| id | name     | email                 | password     | registered          |
+----+----------+-----------------------+--------------+---------------------+
|  2 | Angel    | angel@springville.com | 1PzIjSWmj3   | 2020-02-07 00:00:00 |
|  3 | Carolina | carolina@studio.org   | funnybunny95 | 2020-06-12 00:00:00 |
|  4 | Michael  | michael@mail.com      | 84738257342  | 2020-09-24 00:00:00 |
+----+----------+-----------------------+--------------+---------------------+
3 rows in set (0.00 sec)

As you we can see, the first record has been deleted.

Delete statement without where clause will delete all records:

delete from `user`;

The same result could be achieved with truncate table statement:

truncate table `user`;

A note on auto_increment column

When you delete all data from a table with truncate table command, auto_increment column counter value is reset back to 1. However when you do the same with delete statement, it remains unchanged.

Rate this post:
Share this page:

Learning plan

How to create one or multiple records in a table
How to query records from a table
How to update previously created data in a table
8. Delete statement
How to delete data from a table