> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL DELETE > How the MySQL DELETE statement removes rows, why you should run the WHERE as a SELECT first, and what separates a DELETE from a TRUNCATE TABLE. All examples on this page work on VillageSQL. Install Now → `DELETE` removes whole rows. There is no undo outside a [transaction](/docs/tutorial/transactions), so the habit worth building is to write the `WHERE` first and look at what it selects. These examples use the `sakila_practice` database from [INSERT](/docs/tutorial/insert). Run the setup block there again before you start, then open the client with `mysql -u root -p sakila_practice`. ## How DELETE works ```sql theme={null} DELETE FROM TABLE_NAME WHERE CONDITION; ``` Replace `TABLE_NAME` with the table and `CONDITION` with the test that picks the rows to remove. There is no column list. A `DELETE` takes the entire row, so emptying 1 column is an [UPDATE](/docs/tutorial/update) setting it to NULL. ## Examples ### 1) Look first, then delete Write the `WHERE` as a `SELECT` and read the rows you are about to lose. ```sql theme={null} SELECT member_id, first_name, last_name FROM member WHERE active = 0; ``` ```text theme={null} +-----------+------------+-----------+ | member_id | first_name | last_name | +-----------+------------+-----------+ | 3 | Linda | Williams | +-----------+------------+-----------+ 1 row in set (0.00 sec) ``` One row, and it is the one you meant. Now change the front of the statement and leave the `WHERE` untouched. ```sql theme={null} DELETE FROM member WHERE active = 0; ``` ```text theme={null} Query OK, 1 row affected (0.00 sec) ``` ```sql theme={null} SELECT member_id, first_name, last_name, active FROM member; ``` ```text theme={null} +-----------+------------+-----------+--------+ | member_id | first_name | last_name | active | +-----------+------------+-----------+--------+ | 1 | Mary | Smith | 1 | | 2 | Patricia | Johnson | 1 | +-----------+------------+-----------+--------+ 2 rows in set (0.00 sec) ``` A `DELETE` reports rows affected and nothing else. An `UPDATE` prints a second line with `Rows matched` and `Changed`; a `DELETE` has no equivalent. ### 2) A WHERE that matches nothing is not an error ```sql theme={null} DELETE FROM member WHERE member_id = 99; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) ``` Nothing went wrong and nothing happened. `0 rows affected` after a delete you expected to work means the `WHERE` is wrong, and the statement will not tell you so. ## Without a WHERE it empties the table ```sql theme={null} DELETE FROM member; ``` ```text theme={null} Query OK, 2 rows affected (0.00 sec) ``` ```sql theme={null} SELECT COUNT(*) AS members FROM member; ``` ```text theme={null} +---------+ | members | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) ``` Safe update mode refuses this shape, exactly as it refuses an `UPDATE` with no key in its `WHERE`. [UPDATE](/docs/tutorial/update) shows the `ERROR 1175` it raises, and `mysql -u root -p --safe-updates sakila_practice` turns it on for the whole session. ## TRUNCATE empties it differently The table is empty, so insert a row and look at its id. ```sql theme={null} INSERT INTO member (first_name, last_name, joined) VALUES ('Sam', 'Reed', '2026-06-01'); ``` ```text theme={null} Query OK, 1 row affected (0.00 sec) ``` ```sql theme={null} SELECT member_id, first_name FROM member; ``` ```text theme={null} +-----------+------------+ | member_id | first_name | +-----------+------------+ | 4 | Sam | +-----------+------------+ 1 row in set (0.00 sec) ``` The counter carried on from the rows the `DELETE` removed. `TRUNCATE TABLE` does not. ```sql theme={null} TRUNCATE TABLE member; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) ``` ```sql theme={null} INSERT INTO member (first_name, last_name, joined) VALUES ('Sam', 'Reed', '2026-06-01'); ``` ```text theme={null} Query OK, 1 row affected (0.00 sec) ``` ```sql theme={null} SELECT member_id, first_name FROM member; ``` ```text theme={null} +-----------+------------+ | member_id | first_name | +-----------+------------+ | 1 | Sam | +-----------+------------+ 1 row in set (0.00 sec) ``` `TRUNCATE` drops the table and builds an empty one, which is why it reports `0 rows affected` however many rows it removed and why the counter starts over. It takes no `WHERE`, it cannot be rolled back, and it fires no `DELETE` triggers. Use `TRUNCATE` to reset a table to nothing, and `DELETE` for everything else. ## Summary * Write `DELETE FROM table WHERE condition`, and run the `WHERE` as a `SELECT` first. * Expect a row count and no `Rows matched` line. * Read `0 rows affected` as a `WHERE` that found nothing, not as a failure. * Leave out the `WHERE` and the table is emptied, with no confirmation step. * Use `TRUNCATE TABLE` to empty and reset a table, knowing it cannot be undone. ## See also * [UPDATE](/docs/tutorial/update) — changing rows, and the safe update guard * [WHERE](/docs/tutorial/where) — the clause that decides which rows go * [Transactions](/docs/tutorial/transactions) — the only way to take a DELETE back * [Soft deletes in MySQL](/docs/guides/soft-deletes) — marking rows gone instead of removing them