Level 6 of 13 · Clearing out

SQL DELETE vs TRUNCATE vs DROP, Explained with Examples

Three ways to get rid of data and how they differ: DELETE removes rows, TRUNCATE empties a table, DROP removes the table itself.

Covers: DELETE · WHERE · TRUNCATE · DROP TABLE · IF EXISTS · foreign keys

Practise DELETE & DROP

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Remove rows: DELETE DELETE removes rows from a table, and DROP TABLE removes a whole table. You need them to clear out data nobody wants any more: cancelled orders, test rows, an old table. - `DELETE FROM orders` which table - `WHERE status = 'cancelled'` which rows > Just like UPDATE: run the WHERE as a SELECT first, and check the row count.

DELETE FROM orders
WHERE status = 'cancelled';
SELECT * FROM orders

# DELETE without WHERE DELETE FROM orders with no WHERE removes every row. The table is still there, but empty. > Write the WHERE first, then put DELETE FROM in front of it.

DELETE FROM orders;
SELECT * FROM orders

# Foreign keys protect you Alice has orders, and their customer_id points at her. Deleting her would leave those orders pointing at nobody, so the database refuses. > To remove a customer, delete or move their orders first. ON DELETE CASCADE on the foreign key does it automatically, but only use it when losing the orders is really what you want.

DELETE FROM customers WHERE id = 1

# TRUNCATE and DROP TABLE - `TRUNCATE TABLE` removes every row, faster than DELETE, and restarts AUTO_INCREMENT at 1 - `DROP TABLE` removes the rows and the table itself - `IF EXISTS` only if the table is there, so there's no error when it isn't > In MySQL, TRUNCATE and DROP can't be rolled back, even inside a transaction. Treat both as permanent.

CREATE TABLE junk (id INT);
DROP TABLE IF EXISTS junk;
SHOW TABLES

# Drop the child table first orders points at customers, so customers can't be dropped while orders exists. Drop the child table (orders) first, then the parent (customers). > The same order works for deleting rows: children first, then parents.

DROP TABLE customers

Appian corner

Delete Records (the smart service, or a!deleteRecords) deletes records by primary key, up to 1,000 at a time. Plenty of Appian apps never delete at all: they "soft delete" with an is_active column set to 0, so the history survives and reports still add up.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Delete customer 7 (George, who has no orders).
```
___ FROM customers WHERE id = ___
```
Show the answer
```
DELETE FROM customers WHERE id = 7
```
The keyword, and George’s id.
2. Alice (customer 1) has orders, and orders.customer_id is a FOREIGN KEY. What happens?
```
DELETE FROM customers WHERE id = 1
```
- Alice is deleted
- Alice and her orders are deleted
- An error: her orders still point at her
- Her orders get customer_id NULL
Show the answer
An error: her orders still point at her. Her orders still point at her, so the foreign key blocks the delete.
3. Delete every order that is 'on the way' and has no tip.
Show the answer
```
DELETE FROM orders
WHERE status = 'on the way'
  AND tip IS NULL
```
WHERE status = 'on the way' AND tip IS NULL
4. Delete the Pineapple Anchovy pizza (id 8). Nobody will miss it.
Show the answer
```
DELETE FROM pizzas WHERE id = 8
```
DELETE FROM pizzas WHERE id = 8
5. Empty the error_log table but keep the table itself, the fast way.
Show the answer
```
TRUNCATE TABLE error_log
```
TRUNCATE TABLE, then the table name.
6. What is the difference between DELETE FROM orders and DROP TABLE orders?
- None
- DELETE removes the rows; DROP removes the rows and the table
- DROP removes only the rows
- DELETE also removes the table
Show the answer
DELETE removes the rows; DROP removes the rows and the table. After DELETE the empty table is still there. After DROP it is gone.
7. Boss question! Chloe (customer 3) wants her account deleted. The foreign key will stop you unless her orders go first. Delete her orders, then her.
Show the answer
```
DELETE FROM orders
WHERE customer_id = 3;
DELETE FROM customers WHERE id = 3
```
Two statements: DELETE FROM orders WHERE customer_id = 3; then DELETE FROM customers WHERE id = 3

Practise DELETE & DROP