SQL Delete
DELETE is the SQL command that removes rows from a table. Its WHERE clause picks the rows to remove.
Remove one row
The WHERE clause names one order by its id. The SELECT lists the orders that remain, and order 10 is missing.
DELETE FROM orders
WHERE order_id = 10;
SELECT order_id
FROM orders
ORDER BY order_id;Remove a row by its id
The WHERE clause names the Mouse Pad row by its id. The SELECT lists the seven products left after the delete.
DELETE FROM products
WHERE product_id = 8;
SELECT name, stock
FROM products
ORDER BY product_id;Remove several rows
Two orders carry January dates. One delete removes both rows, and the remaining orders start in February.
DELETE FROM orders
WHERE order_date < '2024-02-01';
SELECT order_id, order_date
FROM orders
ORDER BY order_id;Example
DELETE FROM orders
WHERE quantity = 1;
SELECT order_id, quantity
FROM orders
ORDER BY order_id;Key points
- DELETE FROM: the command that removes rows from a table
- WHERE picks the rows a DELETE removes.
- DELETE without WHERE removes every row and empties the table.
- The table itself stays in place after a DELETE.
- A closing SELECT proves which rows are gone.
In this example, the DELETE statement removes every order with a quantity of one. Five orders match that test. The SELECT lists the orders that remain, in id order. Every quantity in the result is above one, so the matched rows are gone.
Note
DROP TABLE is a different command that removes the whole table, not just its rows.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
What does DELETE without a WHERE clause do?