DELETE: deleting rows
In short: DELETE FROM removes the rows that WHERE selects, and without WHERE it empties the whole table.
Problem
Take my review down
In January Maria wrote a review of the laundry powder: “The box arrived torn.” The shop sent her a new box. Now Maria writes to support and asks for the review to be removed.
The row has to disappear from reviews. Only that one, not Maria's other reviews.
The idea
DELETE FROM … WHERE
DELETE FROM says from which table, and WHERE says which rows:
Review 7 is gone, and Maria's other review, of the white bread, is still there. DELETE removes whole rows. No columns are listed.
The row is deleted only in this database. “Fresh database” brings it back.
Try it
A closed account
Teodora Genova (customer 19) signed up in September but has not ordered anything. Today she closes her account. Delete her.
Solution
DELETE FROM customers WHERE id = 19;DELETE FROM customers WHERE id = 19. 19 customers are left. Teodora has no orders and no reviews, so the database allows it. On the next card it is different.
See it
A customer with orders
Maria wants to close her account too. Try it:
The database refuses. Maria has 12 orders and two reviews, and they point to her with a foreign key (customer_id). If her row disappeared, the orders would belong to nobody.
This is a rule of the table, like UNIQUE and CHECK from the first lesson. In the next module you will write it yourself and choose what happens to the orders when the customer is deleted.
See it
DELETE without WHERE
Forget WHERE, and every row disappears:
16 reviews, collected over a whole year, disappear with one statement. The table stays, but it is empty. Click “Fresh database” to bring them back.
With the customers it does not work that way:
Most customers have orders, so the whole statement is called off. Even the customers without orders are not deleted: either the whole statement runs, or nothing does. So the habit from the previous lesson applies here too: a SELECT with the same WHERE first.
The idea
Soft delete
Georgi Petrov cancels his new order 57. Deleting it does not work, because the order has products in it:
You could delete its rows from order_items first, and then the order itself. But shops rarely do that. Instead the order is cancelled:
This is a soft delete: the row stays, but it is marked as deleted or cancelled. Why shops prefer it:
- The reports stay right. How many orders get cancelled, and why, is an important question.
- Accounting needs every order that had a payment.
- Support sees the history when the customer calls.
- A mistake can be fixed. What is cancelled comes back with one
UPDATE. What is deleted does not come back.
The price is that every query has to remember the cancelled ones. The sales report needs WHERE status <> 'cancelled'.
More precisely, if you are curious
In many systems every table has a column deleted_at: NULL for the live rows, a date for the “deleted” ones. Then every query has WHERE deleted_at IS NULL. You will see how to add a column to a table in the next module.
Sometimes deleting is required. When a person asks for their data to be removed (the “right to be forgotten” in the GDPR), the personal data has to go. Often the name and email are deleted or replaced, and the orders stay without them for the accounts.
Try it
No cucumbers
The cucumbers (product 13) have run out. The shop removes them from the new order 57, and the other products in it stay.
Solution
DELETE FROM order_items WHERE order_id = 57 AND product_id = 13;DELETE FROM order_items WHERE order_id = 57 AND product_id = 13. Both conditions together: without the first, the cucumbers would disappear from the old orders too. The whole order_items table is checked.
Exercise
Two tasks
1. Boris Mitev (boris.mitev@example.com) wants his reviews removed. Delete them, finding the customer by email with a subquery.
Without Boris's reviews:
Solution
DELETE FROM reviews
WHERE customer_id = (SELECT id FROM customers WHERE email = 'boris.mitev@example.com');DELETE FROM reviews WHERE customer_id = (SELECT id FROM customers WHERE email = 'boris.mitev@example.com'). He has one review, of the coffee. 15 are left. Boris himself cannot be deleted: he has orders.
2. The cheese banitsa and the cucumbers have run out and will not be back before the end of the month. Remove the sold-out products (stock 0) from the new orders (status = 'new'). The old orders are history and are not touched.
The new orders without the sold-out products:
Solution
DELETE FROM order_items
WHERE product_id IN (SELECT id FROM products WHERE stock = 0)
AND order_id IN (SELECT id FROM orders WHERE status = 'new');DELETE FROM order_items WHERE product_id IN (SELECT id FROM products WHERE stock = 0) AND order_id IN (SELECT id FROM orders WHERE status = 'new'). Three rows disappear, from orders 27, 46 and 57. Without the second condition 18 rows would disappear, some from delivered orders. The field starts with a SELECT that shows them.
Test