MySQL DELETE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

DELETE removes whole rows. There is no undo outside a transaction, 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. Run the setup block there again before you start, then open the client with mysql -u root -p sakila_practice.

How DELETE works

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 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.

SELECT member_id, first_name, last_name FROM member WHERE active = 0;

+-----------+------------+-----------+
| 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.

DELETE FROM member WHERE active = 0;

Query OK, 1 row affected (0.00 sec)

SELECT member_id, first_name, last_name, active FROM member;

+-----------+------------+-----------+--------+
| 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

DELETE FROM member WHERE member_id = 99;

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

DELETE FROM member;

Query OK, 2 rows affected (0.00 sec)

SELECT COUNT(*) AS members FROM member;

+---------+
| 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 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.

INSERT INTO member (first_name, last_name, joined) VALUES ('Sam', 'Reed', '2026-06-01');

Query OK, 1 row affected (0.00 sec)

SELECT member_id, first_name FROM member;

+-----------+------------+
| 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.

TRUNCATE TABLE member;

Query OK, 0 rows affected (0.00 sec)

INSERT INTO member (first_name, last_name, joined) VALUES ('Sam', 'Reed', '2026-06-01');

Query OK, 1 row affected (0.00 sec)

SELECT member_id, first_name FROM member;

+-----------+------------+
| 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.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How DELETE works

Examples

1) Look first, then delete

2) A WHERE that matches nothing is not an error

Without a WHERE it empties the table

TRUNCATE empties it differently

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How DELETE works

​Examples

​1) Look first, then delete

​2) A WHERE that matches nothing is not an error

​Without a WHERE it empties the table

​TRUNCATE empties it differently

​Summary

​See also

How DELETE works

Examples

1) Look first, then delete

2) A WHERE that matches nothing is not an error

Without a WHERE it empties the table

TRUNCATE empties it differently

Summary

See also