Remove rows with DELETE
Delete just the rows you mean to, and check before you do. About 8 minutes.
DELETE FROM … WHERE
DELETE removes whole rows. The table stays, with fewer rows. Like UPDATE, it needs a WHERE, and without one it removes every row.
There's no column list, because a row can't be partly deleted. If you only want to clear a value, that's an UPDATE setting it to NULL.
DELETE FROM coupons WHERE expires_day < 50 OR used = 1;
| id | code | expires_day | used |
|---|---|---|---|
| 1 | SAVE10 | 30 | 0 |
| 2 | NEW20 | 90 | 0 |
| 3 | FREESHIP | 80 | 1 |
| 4 | FLASH | 10 | 1 |
SAVE10 expires on day 30, before 50. The OR passes, so the row is deleted.
Look first, and think about keeping
Before a DELETE, run the same WHERE as a SELECT. Count the rows and read a few. If the numbers surprise you, you've found a mistake for free.
Also ask whether you should delete at all. Orders, payments and users are often kept and flagged with a deleted_at column instead, so a mistake can be undone and history stays. And mind the brackets when a condition mixes AND with OR.
SELECT COUNT(*) FROM coupons WHERE expires_day < 50 OR used = 1;
What does DELETE FROM coupons; (with no WHERE) do?
- ARemoves every row but keeps the empty table
- BRemoves the table itself
- CNothing, because it needs a WHERE
Show the answer
Removes every row but keeps the empty table. No WHERE matches all rows. The table and its columns remain.
Clear the old coupons
The coupons table has one row per coupon, the day it expires, and used set to 1 once it has been redeemed. Today is day 50. Delete every coupon that has expired (expires_day before 50) or has been used.
coupons(id, code, expires_day, used)
Up to 2,000 coupons